Introduction to Join

Join is a very important operation in SQL statements. The common Join operations are as follows:

mysql officially only provides an inner connection, a left outer connection, and a right outer connection.Other connections can also be made in some ways.

Data preparation

a. Create two tables

create table  `person` (
  `id` int(11),
  `name` varchar(255),
  `city_id` int(11) 
) ;

create table `city` (
  `city_id` int(11) ,
  `city_name` varchar(255)
) ;

b. Insert data

# Insert data into the person table
insert into person values (1, 'name1', 1);
insert into person values (2, 'name2', 2);
insert into person values (3, 'name3', 3);
insert into person values (4, 'name4', 5);

#Insert data into city table
insert into city values (1, 'city1');
insert into city values (2, 'city2');
insert into city values (3, 'city3');
insert into city values (4, 'city4');

1. Left outer connection

A left outer join returns all rows of the left table, or NULL if there are no matching rows in the right table.

select * from city left join person on city.city_id = person.city_id;

Run result:

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|       1 | city1     |    1 | name1 |       1 |
|       2 | city2     |    2 | name2 |       2 |
|       3 | city3     |    3 | name3 |       3 |
|       4 | city4     | NULL | NULL  |    NULL |
+---------+-----------+------+-------+---------+

2. Internal connection

An inner join returns data that is common to both tables.

select * from person inner join city on person.city_id = city.city_id

Run result:

+------+-------+---------+---------+-----------+
| id   | name  | city_id | city_id | city_name |
+------+-------+---------+---------+-----------+
|    1 | name1 |       1 |       1 | city1     |
|    2 | name2 |       2 |       2 | city2     |
|    3 | name3 |       3 |       3 | city3     |
+------+-------+---------+---------+-----------+

3. Right Outer Connection

A right outer join returns all rows of the right table, or NULL if there are no matching rows in the left table.

select * from city right join person on person.city_id = city.city_id;

Run result:

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|       1 | city1     |    1 | name1 |       1 |
|       2 | city2     |    2 | name2 |       2 |
|       3 | city3     |    3 | name3 |       3 |
|    NULL | NULL      |    4 | name4 |       5 |
+---------+-----------+------+-------+---------+

4. Left Connection

The left join is the rest of the A table that gets rid of the contents of the B table, which is a unique part of the A table.It can be seen that the result of the left outer connection is the removal of the common parts of both parties.

select * from city  left join person  on city.city_id = person.city_id where person.city_id is  null;

Run result:

+---------+-----------+------+------+---------+
| city_id | city_name | id   | name | city_id |
+---------+-----------+------+------+---------+
|       4 | city4     | NULL | NULL |    NULL |
+---------+-----------+------+------+---------+

5. Right Connection

The right join is the rest of the table B that gets rid of the contents of table A, which is a unique part of table B.It can be seen that the right outer connection results in the removal of the common parts of both parties.

select * from city  right join person  on city.city_id = person.city_id where city.city_id is null;

Run result:

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|    NULL | NULL      |    4 | name4 |       5 |
+---------+-----------+------+-------+---------+

6. Full Connection

Full join returns all data from both AB tables. mysql does not provide the full join keyword, but it can be done using union, which equals the union of the left outer join and the right outer join.

select * from city left join person on person.city_id = city.city_id
union 
select * from city right join person on person.city_id = city.city_id;

Run result:

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|       1 | city1     |    1 | name1 |       1 |
|       2 | city2     |    2 | name2 |       2 |
|       3 | city3     |    3 | name3 |       3 |
|       4 | city4     | NULL | NULL  |    NULL |
|    NULL | NULL      |    4 | name4 |       5 |
+---------+-----------+------+-------+---------+

7. Difference Set

Difference sets are datasets that do not appear in both tables at the same time, which is actually the union of left and right joins.

select * from city  left join person  on city.city_id = person.city_id where person.city_id is  null 
union
select * from city  right join person  on city.city_id = person.city_id where city.city_id is null;

Run result:

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|       4 | city4     | NULL | NULL  |    NULL |
|    NULL | NULL      |    4 | name4 |       5 |