Oracle SQL-basic multi-Table query usage

Oracle multi-Table query, as its name implies, queries the data you want from multiple tables. Here we will test the emp table and dept table under the scott user. The structure of the two tables is as follows:
 
SQL> select * from dept;
DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------------------------------------------------------------------
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7698 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7566 jones manager 7839 02-APR-81 2975 20
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 30
7782 clark manager 7839 09-JUN-81 2450 10
7788 scott analyst 7566 19-APR-87 3000 20
7839 king president 17-NOV-81 5000 10
7844 turner salesman 7698 08-SEP-81 500 0 30
7876 adams clerk 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------------------------------------------------------------------
7900 james clerk 7698 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10

14 rows selected.

You can use a connection to query data in multiple tables:

The basic statement is as follows:

SELECT table1.column, table2.column from table1, table2 where table1.column1 = table2.column2

There are many types of connections: Equijoins (equivalent join) Non-equijoin (Non-equivalent join) Outer-join (external join) Self-join (Self-join) Cross joins (Cross join) natural joins (Natural connection)

1. Equijoins

Equijoins (equijoin)-use deptno In the emp table to perform equijoin with deptno In the dept table.

SQL> select emp. empno, emp. ename, emp. deptno, dept. deptno, dept. dname from emp, dept where
2 emp. deptno = dept. deptno;

EMPNO ENAME DEPTNO DNAME
------------------------------------------------------
7369 SMITH 20 20 RESEARCH
7499 ALLEN 30 30 SALES
7521 WARD 30 30 SALES
7566 JONES 20 20 RESEARCH
7654 MARTIN 30 30 SALES
7698 BLAKE 30 30 SALES
7782 CLARK 10 10 ACCOUNTING
7788 SCOTT 20 20 RESEARCH
7839 KING 10 10 ACCOUNTING
7844 TURNER 30 30 SALES
7876 ADAMS 20 20 RESEARCH

EMPNO ENAME DEPTNO DNAME
------------------------------------------------------
7900 JAMES 30 30 SALES
7902 FORD 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING

14 rows selected.

 

2. Non-equijoin

Non-equijoin (Non-equivalent join) -- creates a table named job_grades and adds data to it:

SQL> select * from job_grades
2;

GRA LOWEST_SAL HIGHEST_SAL
--------------------------
A 500 1499
B 1500 2499
C 2500 3500

SQL> select emp. ename, emp. sal, job_grades.gra from emp, job_grades where emp. sal
2 job_grades.lowest_sal and job_grades.highest_sal;

ENAME SAL GRA
-------------------------
SMITH 800
WARD 1250
MARTIN 1250
ADAMS 1100
JAMES 950
MILLER 1300
ALLEN 1600 B
CLARK 2450 B
TURNER 1500 B
JONES 2975 C
BLAKE 2850 C

ENAME SAL GRA
-------------------------
SCOTT 3000 C
FORD 3000 C
 

13 rows selected.

1 2 3 Next Page