Detailed explanation of four types of MySQL connections and multi-table queries

create table t_emp(
	id int primary key, 
	name varchar(20),
	deptId int
);
create table t_dept(
	id int primary key,
	name varchar(20)
);
insert into t_dept(id, name) values(1, 'Design Department');
insert into t_dept(id, name) values(2, 'Development Department');
insert into t_dept(id, name) values(3, 'Test Department');
insert into t_emp(id, name, deptId) values(1, '张三', 1);
insert into t_emp(id, name, deptId) values(2, 'Li Si', 2);
insert into t_emp(id, name, deptId) values(3, '王五', 0);
# ps: For the sake of convenience, the t_emp table is referred to as table A and the t_dept table is referred to as table B

SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptId = d.id; 

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId = d.id; 

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId = d.id; 

SELECT * FROM t_emp e LEFT JOIN t_dept d 
ON e.deptId = d.id UNION 
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId = d.id; 

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId= d.id WHERE d.id is null; 

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId= d.id WHERE e.id is null; 

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId = d.id WHERE d.id is null
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId= d.id WHERE e.id is null;