Must understand the SQL knowledge
One, two table outer join query
The existing two table A,b contents are as follows
Mysql> select * from A;
+------+------+
| ID | Col1 |
+------+------+
| 1 | AA |
| 2 | BB |
| 3 | CC |
+------+------+
3 rows in Set (0.00 sec)
mysql> select * from B;
+------+------+
| ID | Col2 |
+------+------+
| 2 | DD |
| 3 | EE |
| 4 | FF |
+------+------+
3 rows in Set (0.01 sec)
1,a table and B table left connection
The left table (A) data is identified, and then according to the conditions on the back, the right table in the normal ID and left table ID is equal to the records are found, and the matching left table records in turn row or multiple lines, if no matching records, display null.
Mysql> SELECT * from A LEFT join B on A.id=b.id;
+------+------+------+------+
| ID | Col1 | ID | Col2 |
+------+------+------+------+
| 1 | AA | NULL | NULL |
| 2 | BB | 2 | DD |
| 3 | CC | 3 | EE |
+------+------+------+------+
3 rows in Set (0.00 sec)
mysql> select a.ID ID, a.col1 C1, B.col2 C2 from A left join B on A.id=b.id;
+------+------+------+
| ID | C1 | C2 |
+------+------+------+
| 1 | AA | NULL |
| 2 | BB | DD |
| 3 | CC | EE |
+------+------+------+
3 rows in Set (0.00 sec)
The following results are the same
2,a table and B table right connection
The right table (B) data are identified, and then according to the following conditions, the left table in the General ID and right table ID is equal to the records are found, and the matching left table record in order to row or multiple rows, if no matching records, display null
Mysql> SELECT * from A right join B on a.id=b.id;
+------+------+------+------+
| ID | Col1 | ID | Col2 |
+------+------+------+------+
| 2 | BB | 2 | DD |
| 3 | CC | 3 | EE |
| NULL | NULL | 4 | FF |
+------+------+------+------+
3 rows in Set (0.07 sec)
mysql> select a.ID ID, a.col1 C1, B.col2 C2 from A right join B on a.id=b.id;
+------+------+------+
| ID | C1 | C2 |
+------+------+------+
| 2 | BB | DD |
| 3 | CC | EE |
| NULL | NULL | FF |
+------+------+------+
3 rows in Set (0.00 sec)