Multi table query, also known as association query, refers to two or more tables completing the query operation together

catalogue

Prerequisites for multi table query

Basic syntax of multi table query

Classification of multi table query

Equivalent connection and non equivalent connection

Self connected and non self connected

Internal connection and external connection (JOIN ON)

Now let's assume that there are two tables, employees and departments, with some duplicate fields

Prerequisites for multi table query

These tables queried together are related (one-to-one, one to many). There must be an associated field between them. This associated field may or may not have a foreign key. For example, employee table and department table are associated by "department number"

Basic syntax of multi table query

With connection conditions

If there are n tables to implement multi table query, at least n - 1 join conditions are required

SELECT employee_id,department_name 
FROM employees,departments 
#Connection conditions
WHERE employees.department_id = departments.department_id;

If a field exists in more than one table in the query statement, you must indicate the table where the field is located

Suggestion: from the perspective of SQL optimization, it is recommended to indicate the table in front of each field when querying multiple tables

SELECT employee_id,department_name,employees.department_id
FROM employees,departments 
WHERE employees.department_id = departments.department_id;

You can alias the table and use the alias of the table in SELECT and WHERE

Note: if an alias is given to a table, once the table name is used in SELECT or WHERE, the alias of the table must be used instead of the original name of the table

SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept 
WHERE emp.department_id = dept.department_id;

Classification of multi table query

Equivalent connection and non equivalent connection

The examples in the above basic syntax are equivalent connections

Let's take an example of non equivalent connection

SELECT emp.employee_id,emp.salary,j.grade_level
FROM employees emp,job_grades j
#Non equivalent connection
WHERE emp.salary BETWEEN j.lowest_sal AND j.highest_sal;

Self connected and non self connected

The examples in the above basic syntax are all non self join

As the name suggests, self connection is to connect your own fields. Let's give an example

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
#Self connection
FROM employees emp,employees mgr 
WHERE emp.manager_id = mgr.employee_id;

Internal connection and external connection (JOIN ON)

Because MySQL does not support SQL 92 syntax, we only introduce SQL 99 syntax supported in MySQL in this article. SQL 99 uses JOIN ON to realize multi table query

Inner join: merge rows of more than two tables with the same column. The result set does not contain rows that do not match one table with another

SELECT last_name,department_name,city
FROM employees e INNER JOIN departments d # INNER can be omitted
ON e.department_id = d.department_id
JOIN locations
ON d.location_id = l.location_id;

JOIN ON: merge the rows of more than two tables with the same column. In addition to the rows matching one table with another table, the result set also queries the unmatched rows in the left table or right table

Left outer join: in addition to the rows that meet the join conditions, the two tables also return the rows that do not meet the conditions in the left table

SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d # OUTER can be omitted
ON e.department_id = d.department_id;

Right outer join: in addition to the rows that meet the join conditions, the two tables also return the rows that do not meet the conditions in the right table

SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d # OUTER can be omitted
ON e.department_id = d.department_id;

FULL OUTER JOIN: MySQL does not support FULL OUTER JOIN, but supports UNION and UNION ALL

Note: less resources are required to execute the UNION ALL statement than the UNION statement. If it is clear that there is no duplicate data in the merged result data, or there is no need to remove the duplicate data, try to use the UNION ALL statement to improve the efficiency of data query

SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;