MySQL connection query

Time:2022-4-5

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;