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

Prerequisite: 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, which are related by department number.

1. Cartesian product (cross connect)

Cartesian product is a mathematical operation. Suppose I have two sets X and y, then the Cartesian product of X and Y is all possible combinations of X and y, that is, the first object comes from X and the second object comes from all possible combinations of Y. The number of combinations is the product of the number of elements in the two sets.

In SQL92, Cartesian product is also called CROSS JOIN and CROSS JOIN in English. CROSS JOIN is also used in SQL99 to represent CROSS JOIN. Its function is to connect any table, even if the two tables are not related.

The error of Cartesian product will occur under the following conditions:
① Omit the join condition (or association condition) of multiple tables.
② The connection condition (or association condition) is invalid.
③ All rows in all tables are connected to each other

In order to avoid Cartesian product, effective connection conditions can be added in WHERE.

After adding connection conditions, query syntax:

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #Connection conditions

2. Equivalent connection vs non equivalent connection

Equivalent connection means that the connection condition is a specific or certain determined value, while non equivalent connection means that the connection condition is a certain or certain range.

2.1 equivalent connection

SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

Extension 1: multiple connection conditions AND and operators

Extension 2: distinguish duplicate column names
When multiple tables have the same column, the column name must be prefixed with the table name.
Columns with the same column name in different tables can be distinguished by table names.

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

Extension 3: table alias
Use aliases to simplify queries.
Using table name prefix before column name can improve query efficiency.

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

It should be noted that if we use the alias of the table, we can only use the alias in the query field and filter criteria instead of the original table name, otherwise an error will be reported.

Extension 4: connecting multiple tables
To connect n tables, at least n-1 connection conditions are required. For example, connecting three tables requires at least two connection conditions.

2.2 non equivalent connection

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

3. Self connected vs non self connected

Self join is the operation of two or more tables that essentially belong to the same table, but they are not logically the same table by aliasing. If it is not self join, the two or more tables operated are essentially different tables.

#This is a self connection to query the employees and their leaders in the employee table (each leader also appears as an employee, so there are leaders)
SELECT worker.last_name ,manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

4. Internal connection vs external connection

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.

External join: in addition to the rows that meet the join conditions, two tables also return the rows that do not meet the conditions in the left (or right) table. This connection is called left (or right) external join. When there is no matching row, the corresponding column in the result table is empty (NULL). If it is a left outer join, the left table in the join condition is also called the master table, and the right table is called the slave table. If it is a right outer connection, the right table in the connection condition is also called the master table, and the left table is called the slave table.

4.1 SQL92 creates connections using (+)
In SQL92, (+) is used to represent the location of the slave table. That is, in the left or right outer join, (+) indicates which is the slave table. Oracle supports SQL92 well, while MySQL does not support the external connection of SQL92.

#Left outer connection
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#Right outer connection
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

Moreover, in SQL92, there are only left external connections and right external connections, and there are no full (or full) external connections.

4.2 implementation of sql99 inner join

SELECT Field list
FROM A surface INNER JOIN B surface #INNER can be omitted
ON Association condition
WHERE Other clauses such as;

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

4.3 SQL99 left outer join

#The query result is A
SELECT Field list
FROM A surface LEFT JOIN B surface
ON Association condition
WHERE Other clauses such as;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

4.4 SQL99 right outer join

#The query result is B
SELECT Field list
FROM A surface RIGHT JOIN B surface
ON Association condition
WHERE Other clauses such as;

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

4.5 SQL99 full outer join
The result of full external connection = data matched by the left and right tables + data not matched by the left table + data not matched by the right table.
SQL99 supports FULL OUTER JOIN, which is implemented using FULL JOIN or FULL OUTER JOIN.
It should be noted that MySQL does not support FULL JOIN, but LEFT JOIN UNION RIGHT JOIN can be used instead.

4.6 use of Union
Merge query results using the UNION keyword, you can give multiple SELECT statements and combine their results into a single result set. When merging, the number of columns and data types corresponding to the two tables must be the same and correspond to each other. SELECT statements are separated by the UNION or UNION ALL keyword.

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

#Query employee information with department number > 90 or email containing a
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

The UNION operator returns the UNION of the result sets of two queries to remove duplicate records.

The UNION ALL operator returns the union of the result sets of two queries. For the duplicate parts of the two result sets, the duplicate is not removed.

Note: less resources are required to execute the UNION ALL statement than the UNION statement. If you clearly know that there is no duplicate data in the result data after merging data, or you do not need to remove the duplicate data, try to use the UNION ALL statement to improve the efficiency of data query.

4.7 implementation of seven SQL JOINS

#Middle figure: inner connection A ∩ B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#Top left: left outer connection
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#Top right: right outer connection
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#Middle left: A - A ∩ B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

#Middle right: B-A ∩ B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

#Bottom left: full external connection
# Middle left + upper right A ∪ B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #No weight removal operation, high efficiency
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#Bottom right
#Middle left figure + middle right figure A ∪ B- A ∩ B or (A - A ∩ B) ∪ (B- A ∩ B)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

5. New features of SQL99 syntax

5.1 natural connection
SQL99 provides some special syntax based on SQL92. For example, NATURAL JOIN is used to represent NATURAL JOIN. We can understand natural connection as equivalent connection in SQL92. It will automatically query all the same fields in the two connection tables, and then perform equivalent connection.
In the SQL92 standard:

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

In SQL99, it can be written as:

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

5.2 USING connection
When we connect, SQL99 also supports equivalent connection USING the fields with the same name in the USING specified data table. However, it can only be used with JOIN. For example:

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

Different from the NATURAL JOIN, USING specifies the specific same field name. You need to fill in the parentheses () of USING with the same name. USING JOIN... USING at the same time can simplify the equivalent connection of JOIN ON. It is the same as the following SQL query results:

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