MySQL DBA system Learning (23) must understand the SQL knowledge

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)