Chapter 6 Association query

6.1 Seven results of associated queries

（1）A∩B
（2）A
（3）A - A∩B
（4）B
（5）B - A∩B
（6）A ∪ B
（7）A∪B- A∩B perhaps (A - A∩B) ∪ （B - A∩B）

6.2 How to achieve ？

1、 Internal connection ： Realization A∩B

select  Field list 
from A surface  inner join B surface 
on  The associated condition 
where  And so on ;
 or 
select  Field list 
from A surface  , B surface 
where  The associated condition  and  And so on ;

Example ：

# Query the name of an employee and the name of his department 
SELECT ename " Name of employee ",dname " Department name "
FROM tb_emp  INNER JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno
 or 
SELECT ename " Name of employee ",dname " Department name "
FROM tb_emp  , tb_dept 
WHERE tb_emp .deptno = tb_dept .deptno

# Query salary higher than 20000 The name of his male employee and the name of his department 
SELECT ename " Name of employee ",dname " Department name "
FROM tb_emp  INNER JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno
WHERE salary>20000 AND gender = ' male '

2、 The left outer join

# The query result is A
select  Field list 
from A surface  left join B surface 
on  The associated condition 
where  And so on ;

# Realization A -  A∩B
select  Field list 
from A surface  left join B surface 
on  The associated condition 
where  Associate fields from the table  is null and  And so on ;

Example ：

# Look up the names of all employees and the names of their departments 
SELECT ename " Name of employee ",dname " Department name "
FROM tb_emp  LEFT JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno


# Check all employees who don't have a department 
SELECT ename " Name of employee ",dname " Department name "
FROM tb_emp  LEFT JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno
WHERE tb_emp .deptno IS NULL

3、 Right connection

# The query result is B
select  Field list 
from A surface  right join B surface 
on  The associated condition 
where  And so on ;

# Realization B -  A∩B
select  Field list 
from A surface  right join B surface 
on  The associated condition 
where  Associate fields from the table  is null and  And so on ;

Example ：

# Check all departments , And employee information for all departments 
SELECT * 
FROM tb_emp  RIGHT JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno

# Look up departments where no employees belong 
SELECT * 
FROM tb_emp  RIGHT JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno
WHERE tb_emp .deptno IS NULL

4、 use union Instead of all external connections

# The query result is A∪B
# Use the left outside A,union  Right outside B
select  Field list 
from A surface  left join B surface 
on  The associated condition 
where  And so on 

union 

select  Field list 
from A surface  right join B surface 
on  The associated condition 
where  And so on ;

# Realization A∪B -  A∩B   or    (A -  A∩B) ∪ （B - A∩B）
# Use the left outside  (A -  A∩B)  union  Right outside （B - A∩B）
select  Field list 
from A surface  left join B surface 
on  The associated condition 
where  Associate fields from the table  is null and  And so on 

union

select  Field list 
from A surface  right join B surface 
on  The associated condition 
where  Associate fields from the table  is null and  And so on 

Example ：

# Query all employees , All departments , Including departments without employees , And employees without departments 
SELECT *
FROM tb_emp  LEFT JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno

UNION

SELECT *
FROM tb_emp  RIGHT JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno

# Look up employees who don't have departments and all departments that don't have employees 

# No department staff 
SELECT *
FROM tb_emp  LEFT JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno
WHERE tb_emp .deptno IS NULL

UNION 

# All departments without staff 
SELECT *
FROM tb_emp  RIGHT JOIN tb_dept 
ON tb_emp .deptno = tb_dept .deptno
WHERE tb_emp .deptno IS NULL

6.3 Special associated queries ： Self join

The tables of two associated queries are the same , By taking the alias to virtual two tables

select  Field list 
from  Table name   Alias 1 inner/left/right join  Table name   Alias 2
on  Alias 1. Correlation field  =  Alias 2 The associated fields of 
where  Other conditions 

Example ：

# Check the employee's number , full name , Salary and his leader's number , full name , Salary 
# It's all in the employee table 
# hold tb_emp  surface , It's like an employee list , As a leader's table again 
# Leadership table is a virtual concept , We can use aliases to virtualize 
SELECT emp.empno " Employee number ",emp.ename " Name of employee " ,emp.salary " Employees' salaries ",
	mgr.empno " The leader's number " ,mgr.ename " The name of the leader ",mgr.salary " Leadership pay "
FROM tb_emp  emp INNER JOIN tb_emp  mgr
#tb_emp  emp： If you use emp., It's an employee 
#tb_emp  mgr： If you use mgr., It means the leader's table 
ON emp.mid = mgr.empno

# Don't add the alias of the table "", Alias the column , It can be used "", Column aliases do not use "" It's fine too , But avoid special symbols like spaces .