Basic Oracle Tutorial: Multi-Table query and SQL99

Multi-Table query and other connections establish connection conditions through two columns with the same meaning in the table. The query results only show that the values in the two columns are the same as those in the row and data tables with the same name selected

Multi-Table query and other connections establish connection conditions through two columns with the same meaning in the table. The query results only show that the values in the two columns are equivalent.

Multi-Table query
And other connections
Create a connection condition by using columns with the same meanings in two tables.
The query results only show the row data with the values of the two columns being equivalent conditions.
The table name prefix must be added when the same column name is selected. Otherwise, you cannot determine which table the column belongs.
N tables have equijoin conditions with at least N-1; otherwise, cartesian products are generated.
Unequal connection
The relationship between A column of data in Table A and one or more columns in Table B is non-equivalent. The conditions such as greater than, less than, and not equal to are in the category of unequal connections.
Self-connection
All data comes from one table. Therefore, you must add an alias to the table in the from clause to add a table alias to the column name in the referenced table.
Essentially, a table is virtualized into two tables.
External Connection
That is, select the rows that meet the same connection conditions and other conditions.
(+) Modifier usage: After the selected result contains only the columns with equal join, the result of the other column is the equivalent row + non-equivalent row.
Full connection (Oracle does not provide connection keywords and must be implemented in set mode)

SQL1999 (SQL99) KEYWORDS with full connection

When data needs to be obtained from multiple tables

Idle> select * from emp, dept;
...
...
56 rows selected.
The result is 56 rows. In fact, the two tables generate a Cartesian set of 14*4.
Idle> select count (*) from emp;

COUNT (*)
----------
14

Idle> select count (*) from dept;

COUNT (*)
----------
4

Idle>

Given an equivalent condition in a connection, it is generally the relationship between the primary key and the foreign key.
For example, deptno is a primary key in the dept table and a foreign key in the emp table.
View the relationship between dept (Department table) and emp (employee table)
They are the master-slave relationship, dept is the master table, and emp is the slave table.
Deptno in emp must belong to deptno in dept.
So the two columns are equivalent.
The specific relationship between the primary key and the foreign key will be detailed later when the table is created.

And other connections

Join all columns in two tables based on the deptno equivalence relationship

Idle> select * from dept, emp where dept. deptno = emp. deptno;

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------------------------------------------------------------
10 accounting new york 7782 clark manager 7839 00:00:00 2450 10
10 accounting new york 7839 king president 1981-11-17 00:00:00 5000 10
10 accounting new york 7934 miller clerk 7782 00:00:00 1300 10
20 research dallas 7566 jones manager 7839 00:00:00 2975 20
20 research dallas 7902 ford analyst 7566 00:00:00 3000 20
20 research dallas 7876 adams clerk 7788 1987-05-23 00:00:00 1100 20
20 research dallas 7369 smith clerk 7902 00:00:00 800 20
20 research dallas 7788 scott analyst 7566 00:00:00 3000 20
30 sales chicago 7521 ward salesman 7698 00:00:00 1250 500 30
30 sales chicago 7844 turner salesman 7698 00:00:00 1500 0 30
30 sales chicago 7499 allen salesman 7698 00:00:00 1600 300 30
30 sales chicago 7900 james clerk 7698 00:00:00 950
30 sales chicago 7698 blke MANAGER 7839 00:00:00 2850
30 sales chicago 7654 martin salesman 7698 1250 00:00:00 1400 30

14 rows selected.

Idle>
This is our result. Cartesian product is almost not required. When multi-table queries are performed, the base database has a where clause to describe the relationship between multiple tables to avoid Cartesian sets.

When two tables have the same column name, you must add the table name prefix before the column to distinguish between them.
Use it directly without conflict
Idle> select deptno, empno, ename, dname, sal from emp a, dept B where B. deptno = a. deptno;
Select deptno, empno, ename, dname, sal from emp a, dept B where B. deptno = a. deptno
*
ERROR at line 1:
ORA-00918: column ambiguously defined
Because deptno does not have a table prefix conflict

Idle>
Fetch data from the two associated tables: Describe the department where scott is located
Idle> select empno, ename, dname, sal from emp, dept where emp. deptno = dept. deptno and ename = 'Scott ';

EMPNO ENAME DNAME SAL
--------------------------------------------
7788 scott research 3000

Idle>

Table alias
Format: Table Name alias
It is necessary to give the table an alias, because some tables have a long name and are not easy to write for reference.

Idle> select a. deptno, empno, ename, dname, sal from emp a, dept B where B. deptno = a. deptno and ename = 'Scott ';

DEPTNO EMPNO ENAME DNAME SAL
------------------------------------------------------
20 7788 scott research 3000

Idle>

Unequal connection
Is exclusive to conditions that are completely equal>, <,! =, <=, >=, Between and
The main reason is that different tables display information of specific ranges (or include relations)
For example, the SALGRADE table divides the salary into five levels.
Idle> select * from SALGRADE;

GRADE LOSAL HISAL
------------------------------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

Idle>

Investigate the wage levels of each employee
Idle> select ename, sal, grade from emp a, salgrade B where sal between losal and hisal;

ENAME SAL GRADE
------------------------------
SMITH 800 1
JAMES 950 1
Adam 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5

14 rows selected.

Idle>

Exercise:
1. query the names of employees whose names start with "S", including employee numbers, Department numbers, department names, and geographical locations.
Select empno, ename, emp. deptno, dept. dname, dept. loc
From emp, dept
Where ename like's % 'and emp. deptno = dept. deptno;
2. query the employee name, Department name, salary, and salary grade.
Select empno, ename, grade
From emp, salgrade
Where sal between losal and hisal;
3. query all employees working in NEW YORK
Select empno, ename, emp. deptno, dept. dname, dept. loc
From emp, dept
Where emp. deptno = dept. deptno and dept. loc = 'New YORK ';

Self-connection
A join query in the same table maps a table into two tables.
It is mainly used for the Self-reference relationship of tables, such as the upper-lower-level or hierarchical relationship in emp.
The table alias must be defined because the custom connection is a link query between the same table.

Example: Based on the relationship between empno and mgr, you can find out who belongs to KING.

Idle> select B. ename | ''s manager is '|. ename from emp a, emp B where. empno = B. mgr and. ename = 'King ';

B. ENAME | ''' SMANAGERIS '| A. ENAME
----------------------------------
BLAKE's manager is KING
JONES's manager is KING
CLARK's manager is KING

Idle>