How to use mysql right outer join query

1. Join the right table as the main table, display all the data in the right table, and query the data in the left table according to the conditions. If satisfied, display. If not satisfied, display in null.

2. It can be understood as ensuring that all data in the right table is displayed based on internal connections.

Grammar

select 字段 from a right [outer] join b on 条件

Example

#左连接
#查询所有部门信息以及该部门员工信息
SELECT did,dname,eid,ename FROM t_department d LEFT JOIN t_employee e
ON d.did=e.dept_id;
 
SELECT did,dname,eid,ename FROM t_employee e LEFT JOIN t_department d
ON d.did=e.dept_id;
 
SELECT did,dname,eid,ename FROM t_department d RIGHT JOIN t_employee e
ON d.did=e.dept_id;
 
UPDATE t_employee SET dept_id=NULL where eid=5;
 
SELECT did,dname,eid,ename FROM t_department d LEFT JOIN t_employee e
ON d.did=e.dept_id;
WHERE e.dept_id IS NULL;
#查询部门信息，保留没有员工的部门信息
SELECT did,dname,eid,ename FROM t_department d RIGHT JOIN t_employee e
ON d.did=e.dept_id;
WHERE e.dept_id IS NULL;
 
SELECT did,dname,eid,ename FROM t_employee e LEFT JOIN t_department d
ON d.did=e.dept_id;
WHERE e.dept_id IS NULL;
#查询所有员工信息，以及员工的部门信息
SELECT eid,ename,did,dname FROM t_employee e LEFT JOIN t_department d
ON e.dept_id=d.did;
 
SELECT eid,ename,did,dname FROM t_department d LEFT JOIN t_employee e
ON e.dept_id=d.did;
 
#查询员工信息，仅保留没有分配部门的员工
SELECT eid,ename,did,dname FROM t_employee e LEFT JOIN t_department d
ON e.dept_id=d.did
WHERE e.dept_id IS NULL;
#查询员工信息，不保留没有分配部门的员工
SELECT eid,ename,did,dname FROM t_employee e LEFT JOIN t_department d
ON e.dept_id=d.did
WHERE e.dept_id IS NOT NULL;
 
SELECT id,ename,tid,dname FROM student_info s LEFT JOIN student_test t
ON s.id_id=t.tid
WHERE t.tid IS NOT NULL;
 
SELECT * FROM student;
SELECT * FROM souce;
#分析 左右外连接
#显示所有学生的测试信息
SELECT st.s_id,name,C,Java FROM student st LEFT JOIN souce s
ON st.s_id=s.s_id;
 
#显示参加考试的学生的测试信息
SELECT st.s_id,name,C,Java FROM student st RIGHT JOIN souce s
ON st.s_id=s.s_id;

The above is the detailed content of How to use mysql right outer join query. For more information, please follow other related articles on the PHP Chinese website!