MySQL multi table query (implemented by SQL joins)

catalogue

multi-table query

multi-table query

introduce:
SELECT.....
FROM...
WHERE...AND/OR/NOT
ORDER BY...(ASC/DESC)
LIMIT...


Which city does the employee named 'Abel' work in?

SELECT *
FROM employees
WHERE last_name='Abel';

SELECT *
FROM departments
WHERE department_id=80;

SELECT *
FROM locations
WHERE location_id=2500;

2. How to query multiple tables?
Cause of error: the connection condition of multiple tables is missing
Wrong implementation: each employee matches each department
A Cartesian product error has occurred

SELECT employee_id,department_name
FROM employees,departments;

3. Correct method of multi table query: connection conditions are required

SELECT employee_id,department_name
FROM employees,departments
#Join condition of two tables
WHERE employees.`department_id`=departments.`department_id`;

4. If a field exists in multiple tables in the query statement, you must specify the table where this field is located.

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

Suggestion: from the perspective of SQL optimization, it is recommended that when querying multiple tables, each field indicates its table.

5. You can alias the table and use the table alias in select and where.
If the table is aliased, once the indication 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`;

6. If there are n tables to implement multi table query, at least n-1 connection conditions are required
Exercise: querying employees_ id,last_ name,department_ name,city

SELECT e.`employee_id`,e.`last_name`,d.`department_name`,l.`city`
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;


Deductive: ask questions 1 --- > solve problems 1 --- > ask questions 2 --- > solve problems 2

Inductive formula: total score

7. Classification of multi table query


Angle 1: equivalent connection   vs   Non equivalent connection

Angle 2: self connection   vs   Non self connection

Angle 3: inner connection   vs   External connection

7.1 equivalent connection   vs   Non equivalent connection example of non equivalent connection

SELECT *
FROM job_grades;

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`;

7.2 self connection   vs   Non self connection

Exercise: query employee name, id and the id and name of their manager

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

7.3 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

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;#Only 106 records

External join: merge the rows of more than two tables with the same column. In addition to the rows that do not match one table with another, the result set also finds the mismatched rows in the left table or right table.

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

Left outer 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 table. This connection is called left outer join
Right outer 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 right table. This connection is called right outer join

Exercise: query the last of all employees_ name,department_ Name information

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`; #Left outer connection required

SQL92 syntax realizes inner connection: see above
SQL92 syntax to realize external connection: using + ----------- MySQL does not support the writing of external connection in SQL92 syntax!

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

The method of JOIN..ON is used in SQL99 syntax to realize multi table query. This method can also solve the problem of external connection. MySQL supports this method.

How to implement multi table query with SQL99 syntax. SQL99 syntax implements inner connection:

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

SELECT e.`employee_id`,e.`last_name`,d.`department_name`,l.`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
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;

Right outer connection:

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

Full outer join: MySQL does not support full outer join

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

8. Use of Union and union all
union: will perform de duplication
union all: the de duplication operation will not be performed
Conclusion: 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.

9. Implementation of 7 kinds of join:

 

Middle: inner connection

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

Top left: left outer connection

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

Top right: right outer connection

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

Middle left:

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;

Right middle figure

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;

Bottom left: full external connection
Method 1: Union all on the left and union all on the right

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 in the middle left and the upper right

Bottom right: union all in the middle left

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;

10. New feature of sql00 syntax 1: natural connection

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`;

natural join: it will help you automatically query all the same fields in two consecutive tables, and then connect them equally.

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

11. New features of sql99 syntax 2: using

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

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

Tags: Database MySQL

Posted on Sat, 20 Nov 2021 12:47:54 -0500 by waskelton4