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 |