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 ;#非等值

Self-join and non-self-join

• 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 ;#自连接

Inner connection and outer connection Connection

• 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

Classification of outer joins: left outer join (more left tables, supplement the right), Right outer join (more right tables, fill in the left), full outer join

SQL92: Use ( ) to create a connection

Inner join: see above

Outer join: left table If there is data mismatch, add ( ) to the right table; otherwise, add ( ) to the left table, but MySQL does not support

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

SQL99: Use JOIN...ON method

inner join

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;

OUTER JOIN

Use OUTER JOIN...ON...

• 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；

The use of UMION

Merge query results

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

• UNION operator

The union of two query results, deduplication (Low efficiency)

• UNION ALL operator (recommended)

The union of two query results without deduplication (high efficiency)

Implementation of 7 kinds of SQL JOINS

Middle picture (inner join):

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

Upper left picture (left outer join):

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

Top right picture (right outer join):

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

Middle left picture:

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;

Middle right picture:

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;

Bottom left picture (full outer join) :

#方式一：左上图 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；

Bottom right picture:

#左中图 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;

New features of SQL syntax

Natural connection

Use keywords: NATURAL JOIN (inflexible), automatic query All the same fields in the table, and then perform an equal value connection

USING connection (not applicable to self-join)

Use the keyword: USING (field with the same name) to automatically join the fields with the same name in the table Equijoining

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!