1. Inner JoinsUse comparison operators to match rows in two tables based on the values of the columns that are common to each table
2. Outer Joins1) Left Join/left outer join the right table without matching, behavior null, left table row reserved 2) Right joins the left table Join/right outer join no match time, behavior nul L, row of right table reserved 3) fully joined full join left table row, right table row fully reserved
3. Example-------------------------------------------------
A table ID name B table ID job parent_id
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu 3 34 4
Relationship between a.ID and parent_id
--------------------------------------------------
1) Internal connection
Select a.*,b.* from a inner join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
2) Left connection
Select a.*,b.* from a LEFT join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu Null
3) Right connection
Select a.*,b.* from a right join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4
4) fully connected
Select a.*,b.* from a full join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4
3 Wang Wu Null
4. Recommended: On only connect operation, where only filter the records of the intermediate tableSELECT T1. C1,t2. Cx,t3.cy
From TAB1 T1
INNER JOIN TAB2 T2 on (T1. C1=t2. C2)
INNER JOIN TAB3 T3 on (T1. C1=t2. C3)
Left OUTER joins TAB4 on (T2. C2=t3. C3);
WHERE T1. X >t3. Y
The above SQL query is a demonstration of multi-table joins.
MySQL connection--Internal connection, outer connection (left, right, full connection)
