MySQL implements an A LEFT JOIN B join_condition as follows:
- Table B is set to depend on table A and all tables on which A depends.
- Table A is set to depend on all tables (except B) that are used in the LEFT JOIN condition.
- The LEFT JOIN condition is used to decide how to retrieve rows from table B. (In other words, any condition in the WHERE clause is not used.)
- All standard join optimizations are performed, with the exception that a table is always read after all tables on which it depends. If there is a circular dependence, MySQL issues an error.
- All standard WHERE optimizations are performed.
- If there is a row in A that matches the WHERE clause, but there is no row in B that matches the ON condition, an extra B row is generated with all columns set to NULL.
- If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: col_name IS NULL in the WHERE part, where col_name is a column that is declared as NOT NULL, MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.
- A Left Join B
- select * from A left join B on A.id = B.id
- For each row as a in A
for each row as b in B
match record if a.id = b.id
end
set b.* to NULL if does not match.
end
- A t1 Left Join A t2
Table A
|
Id |
Num |
Dik |
|
1 |
001 |
2007 |
|
2 |
002 |
2007 |
|
3 |
001 |
2008 |
|
4 |
002 |
2008 |
|
5 |
003 |
2008 |
|
6 |
001 |
2009 |
|
7 |
002 |
2009 |
Select * from A t1 Left Join A t2 ON t1.id <> t2.id AND t1.num = t2.num
+—-+——+——+——+——+——+
| ID | num | dik | ID | num | dik |
+—-+——+——+——+——+——+
| 1 | 001 | 2007 | 3 | 001 | 2008 |
| 1 | 001 | 2007 | 6 | 001 | 2009 |
| 2 | 002 | 2007 | 4 | 002 | 2008 |
| 2 | 002 | 2007 | 7 | 002 | 2009 |
| 3 | 001 | 2008 | 1 | 001 | 2007 |
| 3 | 001 | 2008 | 6 | 001 | 2009 |
| 4 | 002 | 2008 | 2 | 002 | 2007 |
| 4 | 002 | 2008 | 7 | 002 | 2009 |
| 5 | 003 | 2008 | NULL | NULL | NULL |
| 6 | 001 | 2009 | 1 | 001 | 2007 |
| 6 | 001 | 2009 | 3 | 001 | 2008 |
| 7 | 002 | 2009 | 2 | 002 | 2007 |
| 7 | 002 | 2009 | 4 | 002 | 2008 |
+—-+——+——+——+——+——+
13 rows in set (0.00 sec)
Select * from A t1 Left Join A t2 ON t1.id <> t2.id AND t1.num = t2.num AND t1.dik = 2008
+—-+——+——+——+——+——+
| ID | num | dik | ID | num | dik |
+—-+——+——+——+——+——+
| 1 | 001 | 2007 | NULL | NULL | NULL |
| 2 | 002 | 2007 | NULL | NULL | NULL |
| 3 | 001 | 2008 | 1 | 001 | 2007 |
| 3 | 001 | 2008 | 6 | 001 | 2009 |
| 4 | 002 | 2008 | 2 | 002 | 2007 |
| 4 | 002 | 2008 | 7 | 002 | 2009 |
| 5 | 003 | 2008 | NULL | NULL | NULL |
| 6 | 001 | 2009 | NULL | NULL | NULL |
| 7 | 002 | 2009 | NULL | NULL | NULL |
+—-+——+——+——+——+——+
9 rows in set (0.01 sec)
Select * from A t1 Left Join A t2 ON t1.id <> t2.id AND t1.num = t2.num AND t1.dik = 2008 AND t2.dik = 2007
+—-+——+——+——+——+——+
| ID | num | dik | ID | num | dik |
+—-+——+——+——+——+——+
| 1 | 001 | 2007 | NULL | NULL | NULL |
| 2 | 002 | 2007 | NULL | NULL | NULL |
| 3 | 001 | 2008 | 1 | 001 | 2007 |
| 4 | 002 | 2008 | 2 | 002 | 2007 |
| 5 | 003 | 2008 | NULL | NULL | NULL |
| 6 | 001 | 2009 | NULL | NULL | NULL |
| 7 | 002 | 2009 | NULL | NULL | NULL |
+—-+——+——+——+——+——+
7 rows in set (0.00 sec)
Select * from A t1 Left Join A t2 ON t1.id <> t2.id AND t1.num = t2.num AND t1.dik = 2008 AND t2.dik = 2007 WHERE t1.dik = 2008
+—-+——+——+——+——+——+
| ID | num | dik | ID | num | dik |
+—-+——+——+——+——+——+
| 3 | 001 | 2008 | 1 | 001 | 2007 |
| 4 | 002 | 2008 | 2 | 002 | 2007 |
| 5 | 003 | 2008 | NULL | NULL | NULL |
+—-+——+——+——+——+——+
3 rows in set (0.00 sec)