catalogue

1, Why do I need multi table query?

🐟 Which city does an employee named "Abel" work in?

2, How to implement multi table query

1. Error of Cartesian product (cross connection)

① Wrong implementation

② Cause of error

The query method of the table requires multiple conditions

① Join condition of two tables

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

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

🐟 be careful

3, Classification of multi table query

1. Equivalent connection & non equivalent connection

​ ⚪ Non equivalent connection

2. Self connection & non self connection

#Query employee id, employee name and the id and name of its manager - self connection

3. Internal connection & external connection

① Inner connection

② External connection

⚪ SQL92 syntax to realize external connection: Using+

⚪ SQL99 syntax to realize multi table query

Ⅰ. SQL99 syntax to realize inner connection

Ⅱ. SQL99 syntax to realize external connection

Left outer connection:

Right outer connection:

Full external connection:

Ⅲ. Seven JOIN operations are implemented using SQL99

·Figure ③ implementation: internal connection

·Figure ① implementation: left outer connection

·Figure ② implementation: right external connection

·Figure ④ implementation of

·Figure ⑤ implementation

·Figure ⑥ implementation: full external connection

·Figure ⑦ implementation

Ⅳ. Supplement: use of UNION and UNION ALL

1. Merge query results

2.UNION operator

3..UNION ALL operator

4, New features of SQL99 syntax

1. Natural connection

2.USING connection

⚪ There are three ways to constrain table joins: where, on, and using

5, Related tables

1.employees table

2.departments table

​3.locations table

1, Why do I need multi table query?

🐟 Which city does an employee named "Abel" work in?

1. Find out all his information first

SELECT *
FROM employees
WHERE last_name = 'Abel';

2. When knowing that he is in department 80, inquire the information of department 80

SELECT *
FROM departments
WHERE  department_id = 80;

3. The result shows that the city id is 2055

SELECT *
FROM locations
WHERE location_id = 2500;

2, How to implement multi table query

1. Error of Cartesian product (cross connection)

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

① Wrong implementation

Each employee matched with each department

SELECT employee_id,department_name
FROM employees,departments;  #2889 records were found

SELECT employee_id,department_name
FROM employees CROSS JOIN departments;

② Cause of error

• 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

2. The correct way of multi table query - connection conditions are required

① Join condition of two tables

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

SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106 (excluding NULL)

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

SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106 (excluding NULL)

department_id exists in both the employees and departments tables. If it is not specified in which table:

SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106 (excluding NULL)

From the perspective of sql optimization, it is recommended to indicate the table in front of each field when querying multiple tables

SELECT employees.employee_id,departments.department_name,department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106 (excluding NULL)

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

SELECT emp.employee_id,dept.department_name,dept.department_id
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;  #106 (excluding NULL)

🐟 be careful

If a table is aliased, once the alias of the table is used in SELECT or WHERE, the alias of the table must be used instead of the original name of the table

3, Classification of multi table query

1. Equivalent connection & non equivalent connection

Examples of non equivalent connections

SELECT *
FROM job_grades;

⚪ Non equivalent connection

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

2. Self connection & non self connection

#Query employee id, employee name and the id and name of its manager - self connection

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

3. Internal connection & external connection

① Inner connection

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 employee_id,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106 (excluding NULL)

② External connection

In addition to the rows that meet the connection 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) outer connection. When there is no matching row, the corresponding column in the result table is empty (NULL).

Classification of external connection: left external connection, right external connection and full external connection

If it is a left outer connection, the left table in the connection 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.

Query all employees_ id,department_ Name information

⚪ SQL92 syntax to realize external connection: Using+

MySQL does not support the writing of foreign connections in SQL92 syntax

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+);

⚪ SQL99 syntax to realize multi table query

Use join On to realize multi table query. This method can also solve the problem of external connection, and MySQL supports

Ⅰ. SQL99 syntax to realize inner connection

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

Ⅱ. SQL99 syntax to realize external connection

Left outer connection:

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

Right outer connection:

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

Full external connection:

MySQL does not support FULL OUTER JOIN

SELECT employee_id,department_name,city
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

Ⅲ. Seven JOIN operations are implemented using SQL99

·Figure ③ implementation: internal connection

SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

·Figure ① implementation: left outer connection

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

·Figure ② implementation: right external connection

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

·Figure ④ implementation of

Erase the same part on the basis of ①

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE D.department_id IS NULL;

·Figure ⑤ implementation

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

·Figure ⑥ implementation: full external connection

Mode (1): ① UNION ALL ⑤

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

Mode (2): ④ UNION ALL ②

SELECT employee_id,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,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

·Figure ⑦ implementation

④  UNION ALL ⑤ :

SELECT employee_id,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,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

Ⅳ. Supplement: use of UNION and UNION ALL

1. 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 corresponding to each other. SELECT statements are separated by the UNION or UNION ALL keyword.  

2.UNION operator

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

 

3..UNION ALL operator

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 it is clear that there is no duplicate data in the result data after merging 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.

4, New syntax features of SQL99

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 help you automatically query all the same fields in the two connection tables, and then make equivalent connection.

In SQL99, you can write:

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

2.USING connection

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

You can see that different from the NATURAL JOIN, USING specifies the specific name of the same field. You need to fill in the brackets () of USING with the field of the same name to be specified. Use join at the same time USING 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;

⚪ There are three ways to constrain table joins: where, on, and using

WHERE: applicable to all associated queries

ON: can only be used with JOIN, and can only write association conditions. Although Association conditions can be written together with other conditions in WHERE, it is better to write them separately.

USING: it can only be used with JOIN, and the names of the two associated fields are required to be the same in the associated table, and it can only mean that the values of the associated fields are equal

5, Related tables

1.employees table

2.departments table

3.locations table