Detailed explanation of MySQL join multi table connection knowledge

1. Introduction to SQL join multi table connection

SQL Join clause is mainly used in select statements to combine the rows of two or more tables, query based on the common field (often id field) between these tables, and return all rows that meet the conditions from multiple tables.

2. Common join clause types

Common join clause types include INNER JOIN (the same as join), LEFT JOIN, RIGHT JOIN and FULL JOIN. The first one is INNER JOIN and the last three are outer join.

The differences between different join clause types are shown in the following figure:

3. Practical data preparation

#1. Table creation statement and content insertion. a is the table on the left and b is the table on the right
create table a(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);)
create table b(aid int not null,city varchar(20) not null,telnum char(11));
insert into a values(1,'oldboy',35),(2,'oldgirl',28),(3,'inca',22),(5,'zs',23);
insert into b values(1,'bj',135),(2,'sz',189),(3,'sh',166),(4,'hz',187);
#2. Final data results.
mysql> select * from a;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | oldboy  |   35 |
|  2 | oldgirl |   28 |
|  3 | inca    |   22 |
|  5 | zs      |   23 |
+----+---------+------+
4 rows in set (0.00 sec)

mysql> select * from b;
+-----+------+--------+
| aid | city | telnum |
+-----+------+--------+
|   1 | bj   | 135    |
|   2 | sz   | 189    |
|   3 | sh   | 166    |
|   4 | hz   | 187    |
+-----+------+--------+
4 rows in set (0.00 sec)
#Tip: note that the ID columns of the two tables have the same ID and different IDs

4. Inner join practice

Internal connection is the most common connection in production. Only rows with matching Association ID are output. There are three statements of inner connection, all of which are equivalent.

mysql> select * from a join b on a.id=b.aid;         #< = = the inner keyword is omitted.
mysql> select * from a inner join b on a.id=b.aid;   #< = = with inner keyword.
mysql> select * from a,b where a.id=b.aid;

The information output from the above three statements is the same, that is, the row information with the same id in the two tables.

+----+---------+------+-----+------+--------+
| id | name    | age  | aid | city | telnum |
+----+---------+------+-----+------+--------+
|  1 | oldboy  |   35 |   1 | bj   | 135    |
|  2 | oldgirl |   28 |   2 | sz   | 189    |
|  3 | inca    |   22 |   3 | sh   | 166    |

5. Practice of left join

The LEFT JOIN clause will return all the rows in the left table and the rows in the right table that meet the ON Association condition. If the rows in the left table do not match in the right table, the corresponding data in the right table of this row will be replaced with NULL.

mysql> select * from a left join b on a.id=b.aid; #Execute SQL and the output results are as follows.
+----+---------+------+------+------+--------+
| id | name    | age  | aid  | city | telnum |
+----+---------+------+------+------+--------+
|  1 | oldboy  |   35 |    1 | bj   | 135    |
|  2 | oldgirl |   28 |    2 | sz   | 189    |
|  3 | inca    |   22 |    3 | sh   | 166    |
|  5 | zs      |   23 | NULL | NULL | NULL   |
+----+---------+------+------+------+--------+

6. Right join practice

The RIGHT JOIN clause will all the rows in the right table and the rows in the left table that meet the ON Association condition. If the rows in the right table do not match in the left table, the corresponding data in the left table of this row will be replaced with NULL.

mysql> select * from a right outer join b on a.id=b.aid;  #< = = outer can be omitted.
+------+---------+------+-----+------+--------+
| id   | name    | age  | aid | city | telnum |
+------+---------+------+-----+------+--------+
|    1 | oldboy  |   35 |   1 | bj   | 135    |
|    2 | oldgirl |   28 |   2 | sz   | 189    |
|    3 | inca    |   22 |   3 | sh   | 166    |
| NULL | NULL    | NULL |   4 | hz   | 187    |
+------+---------+------+-----+------+--------+
mysql> select * from a right join b on a.id=b.aid;
+------+---------+------+-----+------+--------+
| id   | name    | age  | aid | city | telnum |
+------+---------+------+-----+------+--------+
|    1 | oldboy  |   35 |   1 | bj   | 135    |
|    2 | oldgirl |   28 |   2 | sz   | 189    |
|    3 | inca    |   22 |   3 | sh   | 166    |
| NULL | NULL    | NULL |   4 | hz   | 187    |
+------+---------+------+-----+------+--------+

7. Practice of outer join

Some libraries of outer join are called FULL JOIN, which will return all rows of the left table and the right table. If the data row of one table has no matching row in the other table, the opposite data is replaced by NULL. However, mysql does not support this connection temporarily, but it can be realized by using union all connection, left connection and right connection.

mysql> (select * from a left join b on a.id=b.aid)
    -> union all
    -> (select * from a right join b on a.id=b.aid);
+------+---------+------+------+------+--------+
| id   | name    | age  | aid  | city | telnum |
+------+---------+------+------+------+--------+
|    1 | oldboy  |   35 |    1 | bj   | 135    |
|    2 | oldgirl |   28 |    2 | sz   | 189    |
|    3 | inca    |   22 |    3 | sh   | 166    |
|    5 | zs      |   23 | NULL | NULL | NULL   |
|    1 | oldboy  |   35 |    1 | bj   | 135    |
|    2 | oldgirl |   28 |    2 | sz   | 189    |
|    3 | inca    |   22 |    3 | sh   | 166    |
| NULL | NULL    | NULL |    4 | hz   | 187    |
+------+---------+------+------+------+--------+

8. Old boy's postscript:

Can MySQL join be executed simply? Join, check the connection types and examples supported by mysql. I don't tell him about it.

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}
join_specification: {
    ON search_condition
  | USING (join_column_list)
}

For the real project case of multi table connection, please refer to the old boy's multi table connection practice article: https://www.cnblogs.com/oldboy666/p/15637461.html

Tags: MySQL

Posted on Fri, 03 Dec 2021 14:30:07 -0500 by Gaoshan