Oracle table join, inner join, outer join (left join, right join, full join), implicit join, table union, table intersection, table complement
- 1. Inner join inner join
-
- 1.The difference between and and where
- 2. Inner association implicit writing method (Oracle proprietary)
- 3. Cross connection–Cartesian product connection
- in conclusion:
- 2. Outer connection
-
- 1. left join left join
- 2. right join right join
- 3. full join full join
- 4. Oracle outer join implicit writing method
- 5. Key points
- 3. Self-connection
- 4. Table union UNION
-
- 1.UNION ALL
- 2. UNION
- 3. The difference between UNION ALL and UNION
- 5. Table intersection INTERSECT
- 6. Table complement MINUS
1. inner join
In the inner connection, the database will generally automatically select a table with a small amount of data as the driving table, and then drive each piece of data in a loop, matching it with the driven table according to the associated field after on, and splicing the data.
SELECT T1.EMPNO,T1.JOB,T2.DEPTNO,T2.DNAME,T2.LOC
FROM EMP T1 --driven table
INNER JOIN DEPT T2--Drive table
ON T1.DEPTNO = T2.DEPTNO
Connect the employee table and the department information table through the department number to obtain the data after merging the two tables.
1.The difference between and and where
Example: Find out the department number, department name and region of the department where employee SCOTT is located
---Filter with and
select e.*,t.dname,t.loc
from emp e inner join dept t
on e.deptno=t.deptno
and e.ename = 'SCOTT';
---where filtering
select e.*,t.dname,t.loc
from emp e inner join dept t
on e.deptno=t.deptno
where e.ename = 'SCOTT';
and is the filtering before table connection, and where is the filtering after table association.
2. Inner association implicit writing method (Oracle proprietary)
grammar:
select query field from table 1, table 2 where associated field (where here is equivalent to on) and filter conditions
Note: Although in implicit writing, where is equivalent to on, the two cannot be used equivalently in implicit writing. Implicit writing only allows using where as the association condition between two tables, and using on is an incorrect way of writing.
---Correct writing
select e.*,t.dname,t.loc
from emp e ,dept t
where e.deptno=t.deptno
and e.ename = 'SCOTT'
---wrong writing
select e.*,t.dname,t.loc
from emp e ,dept t
on e.deptno=t.deptno --on error
and e.ename = 'SCOTT'
3. Cross connection – Cartesian product connection
- Note: For related queries (for implicit writing), the related fields must be written, otherwise a Cartesian product will occur (the results of the query will be multiplied);
- If the data volume of the two tables is large, it will cause high load on the database server;
for example:
SELECT * FROM EMP E cross join DEPT D; -- cross join Explicit cross join writing method SELECT * FROM EMP E ,DEPT D ; --Implicit cross connection writing method
To summarize:
- The result set of our inner association query is the intersection of the two tables (matching each other is displayed based on the associated fields).
- Be sure to write related fields, otherwise Cartesian product will occur (the results of the query will be multiplied).
- In the standard writing method, the related fields between tables use ON, and in Oracle’s proprietary implicit writing method, the related fields between tables use WHERE.
2. Outer connection
The difference between outer joins and inner joins:
6. In terms of syntax structure, the inner join is [INNER] JOIN, and the left outer join is LEFT [OUTER] JOIN.
7. As a result, the inner join obtains the intersection of the two tables, and the left outer join obtains all the information of the main table plus the information of the slave table. The data of the master table can be queried even if it does not meet the association conditions. The slave table Unmatched ones are displayed as empty.
8. Master table and slave table. According to the syntax structure of left join: Table 1 is the main table, and Table 2 is the slave table.
1. left join
grammar:
SELECT the fields/columns to be queried
FROM table 1 --- main table
LEFT JOIN table 2 ---from table,
ON related fields
AND filter conditions;
Note:
9. The data in the main table can be queried even if it does not meet the association conditions, which means that all the data in the main table will be displayed.
10. From the table data, the matched ones are displayed normally, and the unmatched ones are displayed as empty.
SELECT *
FROM DEPT D --main table
LEFT JOIN EMP E --from table
ON E.DEPTNO = D.DEPTNO;
2. Right join right join
grammar:
SELECT the fields/columns to be queried
FROM table 1 ---from table
RIGHT JOIN table 2 ---main table,
ON related fields
AND filter conditions;
The right join takes table 2 as the main table and displays all field contents of table 2. If it does not match, it will be displayed as null.
SELECT *
FROM EMP E --from table
RIGHT JOIN DEPT D --Main table
ON E.DEPTNO = D.DEPTNO;
3. full join
Full foreign association, there is no master-slave table, the two tables are compared with each other, the same parts are displayed normally, and the different parts are NULL.
grammar:
SELECT the fields/columns to be queried
FROM table 1
FULL JOIN table 2
ON related fields
AND filter conditions;
Full outer join–displays all data from two tables
SELECT e.*,t.*
FROM EMP e
FULL JOIN DEPT t
ON e.deptno = t.deptno
4. Implicit writing of Oracle outer joins
- left join
SELECT * FROM emp e,dept f where e.deptno=f.deptno( + ) -- ( + ) is on the right, and the emp table on the other side is the main table --AND filtering conditions; --filtering after table connection is completed
2. Right join
SELECT * FROM emp e,dept f where e.deptno( + )=f.deptno -- ( + ) is on which side, the dept table on the other side is the main table --AND filter condition -- is the filter after the table connection is completed
5. Key points
- When using external joins, you need to pay attention to: the choice between the master table and the slave table (you need to determine which table is the master table and which table is the slave table).
- When connecting externally. Useandfilter conditionsnotto filter the information in the main table. When using where, some information from the main table will be filtered out.
- When connecting tables using external joins, most of them use and as filter conditions – key points
Join query summary:
- Inner join: Take the intersection of two tables. There is no distinction between master and slave tables
- Outer join:
- a. Left outer join: The table to the left of the keyword LEFT is the master table (divided into master and slave tables), which displays all the information of the master table and the related information from the slave table. Unmatched parts of the table are completed with null.
- b. Right outer join: The table on the right of the keyword RIGHT is the master table (divided into master and slave tables), which displays all the information of the master table and part of the information of the slave table. If the slave table does not match Partially completed with null
- c. Full outer join full: displays the same information of the two tables, and uses null to complete the different parts. The data of the two tables can be queried even if they are not related, and all the data of the two tables will be displayed. information.
3. Self-connection
The difference between inner join auto-association and outer join auto-association.
Example: Perform table self-join association through superior leader number.
- Inner join – table self-relation
SELECT T1.EMPNO,T2.EMPNO,T2.ENAME--Supervisor employee number and name
FROM EMP T1 --Get employee data
INNER JOIN EMP T2 --Get leadership data
ON T1.MGR = T2.EMPNO --Using inner join will filter out the chairman's data
2. Outer join-table self-association
SELECT T1.EMPNO,T2.EMPNO,T2.ENAME--Supervisor employee number and name
FROM EMP T1 ---Get employee data
LEFT JOIN EMP T2 ---Get leadership data
ON T1.MGR = T2.EMPNO --Change to left join to get the chairman's data
4. Table union UNION
1. UNION ALL
There are 14 pieces of data in the emp table. After merging all tables, 28 pieces of data are obtained.
SELECT * FROM EMP -- Pay attention to the order of the fields in the upper and lower SQL result sets, and the quantities must be consistent UNION ALL SELECT * FROM EMP;
2. UNION
SELECT * FROM EMP UNION -- splice the upper and lower SQL result sets and sort them without duplication (the default is ascending order); SELECT * FROM EMP;
3. The difference between UNION ALL and UNION
- UNION ALL simply splices the upper and lower SQL result sets without deduplication and sorting. –higher efficiency
- UNION splices the upper and lower SQL result sets and performs deduplication sorting (the default is ascending order); –lower efficiency
Note: The order and quantity of fields in the upper and lower SQL result sets must be consistent.
5. Table intersection INTERSECT
Table intersection INTERSECT returns query records common to both queries
SELECT DEPTNO FROM EMP INTERSECT SELECT DEPTNO FROM DEPT;
6. Table complement MINUS
Table complement can also be considered as having the function of subtraction;
- MINUS returns the remaining records after subtracting the records from the first query and the records from the second query.
- It can also be understood as taking out the records that are in the first record but not in the second record;
--There are only departments (10, 20, 30) in the emp table, and (10, 20, 30, 40) in the department table, so the complement result is empty SELECT DISTINCT DEPTNO FROM EMP MINUS SELECT DEPTNO FROM DEPT; --The dept department table only has departments (10, 20, 30, 40), while the emp employee table has (10, 20, 30), so the complement result is 40 SELECT DEPTNO FROM DEPT MINUS SELECT DISTINCT DEPTNO FROM EMP;
