MySQL > JOIN collation

To prepare data

create table `student`(
  `id` int not null auto_increment,
  `class_id` int not null,
  `name` varchar(255) not null.primary key (`id`)
);
create table `class`(
  `id` int not null auto_increment,
  `name` varchar(255) not null.primary key (`id`)
);
insert into `student`(`id`, `class_id`, `name`) values(1.0.'name1');
insert into `student`(`id`, `class_id`, `name`) values(2.2.'name2');
insert into `student`(`id`, `class_id`, `name`) values(3.3.'name3');
insert into `student`(`id`, `class_id`, `name`) values(4.3.'name4');

insert into `class`(`id`, `name`) values(1.'1');
insert into `class`(`id`, `name`) values(2.Class '2');
insert into `class`(`id`, `name`) values(3.'class 3');
insert into `class`(`id`, `name`) values(4.'class 4');
Copy the code

category

$A$

The left outer join is based on the data in the left table, and NULL is added if there is no corresponding record in the right table.

SELECT
	`s`.`id` AS `student.id`,
	`s`.`class_id` AS `student.class_id`,
	`s`.`name` AS `student.name`,
	`c`.`id` AS `class.id`,
	`c`.`name` AS `class.name` 
FROM
	`student` AS `s`
	LEFT JOIN `class` AS `c` ON `s`.`class_id` = `c`.`id`;
Copy the code

+------------+------------------+--------------+----------+------------+ | student.id | student.class_id | student.name | class.id | class.name | +------------+------------------+--------------+----------+------------+ | 1 | 0 | name1 | NULL | NULL | | 2 | 2 | name2 class | 2 | 2 | 3 | 3 | | name3 class | 3 | 3 | | | 3 | 4 name4 | | 3 | 3 class +------------+------------------+--------------+----------+------------+Copy the code

$B$

It is symmetrically connected to the left

select * from `class` right join `student` on `student`.`class_id` = `class`.`id`;
Copy the code

Equivalent to the left join SQL above.

+------+-------+----+-------+
| id   | name  | id | name  |
+------+-------+----+-------+
|    3 | name3 |  3 | name3 |
|    4 | name4 |  4 | name4 |
| NULL | NULL  |  5 | name5 |
| NULL | NULL  |  6 | name6 |
+------+-------+----+-------+
Copy the code

$A\cap B$

In theory, this is called an inner join, which is to query the intersection of conditions between two tables.

For MySQL, the following SQL statements have the same effect:

SELECT
	`s`.`id` AS `student.id`,
	`s`.`class_id` AS `student.class_id`,
	`s`.`name` AS `student.name`,
	`c`.`id` AS `class.id`,
	`c`.`name` AS `class.name` 
FROM
	`student` AS `s`
	INNER JOIN `class` AS `c` ON `s`.`class_id` = `c`.`id`;
Copy the code

SELECT
	`s`.`id` AS `student.id`,
	`s`.`class_id` AS `student.class_id`,
	`s`.`name` AS `student.name`,
	`c`.`id` AS `class.id`,
	`c`.`name` AS `class.name` 
FROM
	`student` AS `s`
	CROSS JOIN `class` AS `c` ON `s`.`class_id` = `c`.`id`;
Copy the code

SELECT
	`s`.`id` AS `student.id`,
	`s`.`class_id` AS `student.class_id`,
	`s`.`name` AS `student.name`,
	`c`.`id` AS `class.id`,
	`c`.`name` AS `class.name` 
FROM
	`student` AS `s`
	JOIN `class` AS `c` ON `s`.`class_id` = `c`.`id`;
Copy the code

+------------+------------------+--------------+----------+------------+ | student.id | student.class_id | student.name | class.id | class.name | +------------+------------------+--------------+----------+------------+ | 2 | 2 | name2 | 2 | Class 2 | | 3 | 3 | name3 class | 3 | 3 | | | 3 | 4 name4 | | 3 | 3 class +------------+------------------+--------------+----------+------------+Copy the code

$A\setminus B$

SELECT
	`s`.`id` AS `student.id`,
	`s`.`class_id` AS `student.class_id`,
	`s`.`name` AS `student.name`,
	`c`.`id` AS `class.id`,
	`c`.`name` AS `class.name` 
FROM
	`student` AS `s`
	LEFT JOIN `class` AS `c` ON `s`.`class_id` = `c`.`id` 
WHERE
	`c`.`id` IS NULL;
Copy the code

+------------+------------------+--------------+----------+------------+
| student.id | student.class_id | student.name | class.id | class.name |
+------------+------------------+--------------+----------+------------+
|          1 |                0 | name1        |     NULL | NULL       |
+------------+------------------+--------------+----------+------------+
Copy the code

Select * from student where class_id is the primary key of the class table. For some historical reasons, the value of class_id may be 0, indicating that the student has no class. If we need to find students without classes from the student table, we can use the SQL above.

$B\setminus A$

SELECT
	`s`.`id` AS `student.id`,
	`s`.`class_id` AS `student.class_id`,
	`s`.`name` AS `student.name`,
	`c`.`id` AS `class.id`,
	`c`.`name` AS `class.name` 
FROM
	`class` AS `c`
	LEFT JOIN `student` AS `s` ON `s`.`class_id` = `c`.`id` 
WHERE
	`s`.`id` IS NULL;
Copy the code

+------------+------------------+--------------+----------+------------+ | student.id | student.class_id | student.name | class.id | class.name | +------------+------------------+--------------+----------+------------+ | NULL | NULL | NULL Class | | 1 | | NULL | NULL | NULL | | | class 4 + 4 -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

The SQL is used to find classes with no students

$(A\cup B)\setminus (A\cap B)$

select * from a full outer join b on a.id = b.id
where a.id is null or b.id is null;
Copy the code

However, MySQL does not have the FULL keyword, so it is implemented using UNION

select * from `student` left join `class` on `student`.`class_id` = `class`.`id` where `class`.`id` is null
union
select * from `student` right join `class` on `student`.`class_id` = `class`.`id` where `student`.`id` is null
Copy the code

+------+----------+-------+------+-------+ | id | class_id | name | id | name | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 0 name1 | NULL | NULL | | NULL | NULL | NULL | | | 1 class | NULL | NULL | NULL | | 4 class 4 | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

$A\cup B$

Theoretically, it belongs to the whole connection and take the whole set

select a.id a_id, a.name a_name, b.id b_id, b.name b_name
from a left join b on a.id = b.id
union
select a.id a_id, a.name a_name, b.id b_id, b.name b_name
from a right join b on a.id = b.id
Copy the code