11.5 connection query (column connection)
Question: there are ten pieces of data in the student table and ten pieces of data in the grade table.
How many pieces of data are there after connection (Cartesian product): 10 * 10 = 100
Continuous query will produce Cartesian product; Suppose set a = {a, B}, set B = {C, D}
Cartesian product of two sets {(a, c), (a, d) (B, c), (B, d)}
However:
(1) In terms of data results, there are a lot of useless data.
(2) Filter useless data through the where condition: clear useless data through the main foreign key
##Use the main foreign key to clear useless data
select * from emp,dept where emp.deptno = dept.deptno;
11.5.1 internal connection
The above method is actually internal connection, but it is not in the SQL standard
##Standard writing
select
*
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
(1) Inner ， can be omitted
(2) Characteristics of internal connection: only those that meet the conditions will be left, and the data that does not meet the conditions will be lost
If the Department is 40, there is no data. There are no people in this department in the employee table emp
11.5.2 external connection
A left join b: a left join b} table; Table a is the main table
B left join a: B left join a{table; Table B is the main table
A right join b: a right join b} table; Table B is the main table
B right join a: b right join a{table; Table a is the main table
##Employee table emp is the main table to match Dept
select * from
emp e
left join
dept d
on
e.deptno = d.deptno;
##The Department table is the main table to match the employee table
select * from emp e
right join
dept d
on e.deptno = d.deptno;
Features: all the data in the main table are, and then match the data in the non main table, and the matching is null
Interview question: the difference between left connection and right connection
11.5.3 natural connection
In the two tables, the field names and data types are the same, which will naturally serve as the conditions for connection
##Natural connection
select * from emp e
natural join
dept d;
