Oracle learning notes-4 Multi-table queries

1. retrieve data from multiple tables
Select table1.column, table2.column
From table, Table2
Where table1.column1 = table2.column2;
 
-Write join conditions in the WHERE clause
-If the same column name appears in multiple tables, you must use the table name as the prefix of the column name from the table.
-When n tables are connected, at least N-1 connection conditions are required

2. Cartesian Product
Cartesian products are generated in the following situations:
-The connection condition is omitted.
-The connection condition is invalid.
-All rows in the first table are connected to all rows in the second table.

3. Connection Type
1) equijoin
-Use the table name as the prefix to limit columns when multiple tables are used.
-You can use the table prefix to improve the performance.
-Differentiate columns with the same name from different tables by the table name and column name.
-Simplify the query statement by using the table alias
Example 1:
Select EMP. empno, EMP. ename, EMP. deptno, Dept. deptno, Dept. Loc
From EMP, Dept
Where EMP. deptno = Dept. deptno;

Example 2:
Select E. empno, E. ename, E. deptno, D. deptno, D. Loc
From emp e, DEPT d
Where E. deptno = D. deptno;

2) Non-equivalent join
Example:
Select E. ename, E. Sal, S. Grade
From emp e, salgrade s
Where E. Sal
Between S. losal and S. hisal;

3) External Connection
-External Connection conditions cannot use the in operator or use the OR operator to connect to other conditions.
Example:
Select E. ename, D. deptno, D. dname
From emp e, DEPT d
Where E. deptno (+) = D. deptno
Order by E. deptno;

4) Self-connection
Example:
Select worker. ename | 'Works for '| manager. ename
From EMP worker, EMP Manager
Where worker. Mgr = manager. empno;

4. SQL: 1999 syntax connection
Select table1.column, table2.column
From Table1
[Cross join Table2] |
[Natural join Table2] |
[Join Table2 using (column_name)] |
[Join Table2
On (table1.column _ name = table2.column _ name)] |
[Left | right | Full outer join Table2
On (talble1.column _ name = table2.column _ name)];

1) create a cross connection
The cross join clause generates the cross product of two tables, which is the same as the Cartesian product between two tables.

Example:
Select EMP. empno, EMP. ename, EMP. Sal, EMP. deptno, Dept. Loc
From EMP
Cross join dept;

2) natural connection
-The Natural join clause is based on two columns with the same name in the table.
-Records with the same values in the matching columns of the two tables are returned.
-If the data types of Columns with the same name are different, an error occurs.

Example 1:
Select empno, ename, Sal, deptno
From EMP
Natural join dept;
Result:
Empno ename Sal deptno Loc
7369 Smith 800 20 Dallas
7499 Allen 1600 30 Chicago
7521 ward 1250 30 Chicago
7566 Jones 2975 20 Dallas
7654 Martin 1250 30 Chicago
7698 Blake 2850 30 Chicago
7782 Clark 2450 10 New York
...

Example 2:
Select empno, ename, Sal, deptno, Loc
From EMP
Natural join Dept
Where deptno in (10, 20 );
Result:
Empno ename Sal deptno Loc
7369 Smith 800 20 Dallas
7566 Jones 2975 20 Dallas
7788 Scott 3000 20 Dallas
7876 Adams 1100 20 Dallas
7902 Ford 3000 20 Dallas
7782 Clark 2450 10 New York
7839 King 5000 10 New York
7934 Miller 1300 10 New York

3) use the using clause to create a connection
-The using clause can be used to specify the columns that generate connections.
Example:
Select E. ename, E. Sal, deptno, D. Loc
From EMP E
Join dept D using (deptno)
Where deptno = 20;
Result:
Ename Sal deptno Loc
Smith 800 20 Dallas
Jones 2975 20 Dallas
Scott 3000 20 Dallas
Adams 1100 20 Dallas
Ford 3000 20 Dallas
...

Note:
-If several columns have the same names but different data types, the natural join clause can be replaced with the using clause to specify the columns that generate equijoin.
-If multiple columns match, use the using clause to specify only one of the columns.
-The columns used in the using clause cannot use the table name and column name as the prefix.
-The Natural join clause and the using clause are mutually exclusive and cannot be used at the same time.

4) use the on clause to create a connection
-The natural connection condition is basically equivalent join between tables with the same column name. to specify any join condition or column to be connected, you can use the on Clause
-Use on to separate connection conditions from other search conditions
-The on clause can improve code readability.
Example 1:
Select E. empno, E. ename, E. deptno, D. deptno, D. Loc
From EMP E
Join dept d
On (E. deptno = D. deptno );
Result:
Empno ename deptno Loc
7369 Smith 20 20 Dallas
7499 Allen 30 30 Chicago
7521 ward 30 30 Chicago
7566 Jones 20 20 Dallas
...

Example 2:
Select E. empno, E. ename, D. Loc, M. ename
From EMP E
Join dept d
On E. deptno = D. deptno
Join EMP m
On E. Mgr = M. empno;
Result:
Empno ename loc ename
7369 Smith Dallas Ford
7499 Allen Chicago Blake
7521 ward Chicago Blake
7566 Jones Dallas king
7654 Martin Chicago Blake
7698 Blake Chicago king
7782 Clark New York king
7788 Scott Dallas Jones
7844 Turner Chicago Blake
7876 Adams Dallas Scott
7900 James Chicago Blake
Ford Dallas Jones 7902
7934 Miller New York Clark

5) left Outer Join
Example:
Select E. ename, E. deptno, D. Loc
From EMP E
Left Outer Join dept d
On (E. deptno = D. deptno );
Result:
Ename deptno Loc
Miller 10 New York
King 10 New York
Clark 10 New York
Ford 20 Dallas
...
Ward

6) Right Outer Join
Example:
Select E. ename, E. deptno, D. Loc
From EMP E
Right Outer Join dept d
On (E. deptno = D. deptno );
Result:
Ename deptno Loc
Smith 20 Dallas
Allen 30 Chicago
Ward 30 Chicago
Jones 20 Dallas
...
Boston

7) All external connections
Example:
Select E. ename, E. deptno, D. Loc
From EMP E
Full outer join dept d
On (E. deptno = D. deptno );
Result:
Ename deptno Loc
Miller 10 New York
King 10 New York
Clark 10 New York
Ford 20 Dallas
...
Ward
Boston

Exercise
1. query the EMP and dept tables and display information of all empno, ename, deptno, and dname columns that match the first letter of the ename column "S ".

Select empno, ename, D. deptno, dname
From emp e, DEPT d
Where E. deptno = D. deptno
And ename like's % ';

Or:
Select EMP. empno, EMP. ename, EMP. deptno, Dept. dname
From EMP, Dept
Where EMP. deptno = Dept. deptno
And substr (EMP. ename, 1, 1) ='s ';

2. query the EMP, salgrade, and dept tables to display the employee name, Department name, salary, and salary grade.

Select E. ename, D. deptno, E. Sal, S. Grade
From emp e, dept d, salgrade s
Where E. deptno = D. deptno
And (E. Sal between S. losal and S. hisal );

3. display information about all employees working in New York

Select *
From emp e, DEPT d
Where E. deptno = D. deptno
And D. Loc = 'New York ';

4. query the EMP table and display the employee name and employment time before employee 'Scott '.

Select a. ename, A. hiredate, B. ename, B. hiredate
From emp a, EMP B
Where a. hiredate <B. hiredate and B. ename = 'Scott ';

5. display information about employees in all departments. If there are no employees in the department, the information about the department is also displayed.

Select *
From emp e, DEPT d
Where E. deptno (+) = D. deptno;