MySQL multi-table query case analysis

Multiple table query

Case description

Understanding of Cartesian product

select id,department_name
from employees,departments;#错的

select id,department_id,department_name
from employees CROSS JOIN departments;#错的

Each employee and each department were matched once (number of entries found = Number of ids * number of departments)

Cause of error: missing connection conditions

Solution to Cartesian product

Write connection conditions: Table 1. Column = Table 2. Column (if To connect multiple tables, at least n-1 connection conditions must be used)

select id,employees.name,department_name 
from employees,departments
WHERE employees.name = departments.name;

Note: If the column to be displayed has the same name in the table to be queried, it must be indicated which table it comes from, eg: employees .name

It is recommended to indicate which table information is displayed when querying multiple tables (optimization)

Optimization: You can use the alias of the table after FROM, but once the alias is used, subsequent Be sure to use aliases

Classification of multi-table queries

Equivalent joins and non-equivalent joins

• Equivalent joins: The above ones with =

• ##Non-equijoin: No=

select t1.id,t1.name,t2.grade
from employees t1,departments t2
WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary ;#非等值

Copy after login

• Non-self-link: Table 1 and Table 2 are connected

• Self-link: Table 1 and itself are connected

#显示员工（t1）和其管理者（t2）的基本信息
select t1.id,t1.name,t2.id，t2.name
from employees t1,employees t2#一个表看作两个表
WHERE t1.manage_id = t2.id ;#自连接

Copy after login

• Inner join: merge tables containing the same column, the result does not include rows that do not match one table with another table

• Except Join: Merge tables containing the same column. In addition to the results of the inner join, unmatched rows are also queried

WHERE t1.department_id = t2.department_id(+)#左连接

select t1.id,t1.name,t2.department_name,t3.environment
from employees t1 JOIN departments t2
ON t1.department_id = t2.department_id
JOIN locations t3#加入第二个人表
ON t2.department_location = t3.department_location;

Copy after login

• Left outer join: LEFT OUTER JOIN

• RIGHT OUTER JOIN

• Full OUTER JOIN: FULL OUTER JOIN (MySQL does not support)

select t1.name,t2.department_name#左外连接
from employees t1 LEFT OUTER（可省略） JOIN departments t2
ON t1.department_id = t2.department_id；

Copy after login

SELECT colum... FROM table1
UNION (ALL)
SELECT colum... FROM table2

• UNION operator

• UNION ALL operator (recommended)

select t1.name,t2.department_name
from employees t1 JOIN departments t2
ON t1.department_id = t2.department_id；

select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id；

select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id；

select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL;

select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.department_id IS NULL;

#方式一：左上图 UNION ALL 右中图
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
UNION ALL 
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.department_id IS NULL;

#方式二：左中图 UNION ALL 右上图
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL
UNION ALL
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id；

#左中图 UNION ALL 右中图
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL
UNION ALL
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.department_id IS NULL;

select t1.name,t2.department_name
from employees t1 JOIN departments t2
ON t1.department_id = t2.department_id;
等价于
select t1.name,t2.department_name
from employees t1 JOIN departments t2
USING(department_id);

The above is the detailed content of MySQL multi-table query case analysis. For more information, please follow other related articles on the PHP Chinese website!