MySQL multi-table query detailed explanation

#2, SQL1999 syntax:
 SELECT query list FROM table1 alias [connection type]
 JOIN table 2 alias ON link condition [WHERE filter condition]
 GROUP BY
 【HAVING filter conditions】
 【ORDER BY sort list ASC|DESC】

Category (connection type):
 Inner join (★): INNER
 Outer join left outer (★): LEFT 【OUTER】
 Right outer (★): RIGHT 【OUTER】
 FULL 【OUTER】
 Cross connection: CROSS

1. Inner join syntax:
 SELECT query list FROM table 1 alias INNER JOIN table 2 alias ON join condition [WHERE filter condition]
 GROUP BY
 【HAVING Filter Group】
 【ORDER BY sort list ASC|DESC】

Classification:
 Characteristics of equal and non-equal value self-connection:
 ①Add sorting, grouping, and filtering ②INNER can be omitted ③Put the filtering conditions after WHERE and the connection conditions after ON to improve separation and facilitate reading.
 ④The effect of INNER JOIN connection is the same as the equivalent connection in SQL1992 syntax, both of which query the intersection part.

#1. Equivalent connection #Case 1. Query employee name and department name. Swapping the join conditions does not affect the results.
SELECT last_name AS employee_name,department_name AS department_name FROM employees_name
INNER JOIN departments d
ON e.department_id=d.department_id;
SELECT last_name AS employee_name,department_name AS department_name FROM employees_name
INNER JOIN departments e
ON e.department_id=d.department_id; 

#Case 2. Query employees and job names containing "e" (screening)
SELECT last_name AS employee name, job_title AS job title FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.last_name LIKE '%e%'; 

#Case 3. Query the city names and department numbers where the number of departments is > 3. (Grouping + Filtering)
Step by step: first find out the number of departments in each city, and then filter those that meet the requirements.
SELECT city AS city,COUNT(*) AS number FROM locations l
INNER JOIN departments d
ON l.location_id=d.location_id
GROUP BY city
HAVING COUNT(*) > 3; 

#Case 4. Query the department name and number of employees whose number of employees is greater than 3, and sort them in descending order.
Step by step:
1. Query the number of employees in each department,
2. Filter out the records with more than 3 employees in the above results.
3. Sort the number of employees SELECT COUNT(*) AS number of employees, d.department_name AS department name FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY d.department_name
HAVING COUNT(*) > 3
ORDER BY employee_number DESC; 

#Case 5. Query employee name, department name, job name, and sort by department name in descending order. Pay attention to the conditions when connecting the three tables.
SELECT last_name AS employee name, department_name AS department name,
job_title AS job name FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN jobs j
ON e.job_id=j.job_id
ORDER BY d.department_name DESC; 

#2. Non-equivalue connection. Range (indirect)
#Case 1. Query the salary level of employees.
SELECT salary AS monthly salary, grade_level AS salary grade FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal; 

#Case 2. Query the number of each salary level > 20 and sort them in descending order.
SELECT j.grade_level AS grade,COUNT(*) number FROM employees e
INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY j.grade_level
HAVING COUNT(*) > 20
ORDER BY number DESC; 

#3.Self-join#Case 1. Query the employee's name and the supervisor's name.
SELECT e.last_name AS employee name, m.last_name AS supervisor name FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id; 

#Case 2. Query the employee's name and the supervisor's name, which contains the character k.
SELECT e.last_name AS employee name, m.last_name AS supervisor name FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id
WHERE e.last_name LIKE '%k%'; 

#2. External join application scenario: used to query records that exist in one table but not in another table.
Features:
1. The query result of the outer join is all the records in the main table. If there is a match in the secondary table, the matching value is displayed. If there is no match in the secondary table, null is displayed.
Outer join query result = inner join result + records in the main table but not in the slave table 2. Left outer join, the left side of LEFT JOIN is the main table Right outer join, the right side of RIGHT JOIN is the main table 3. The same effect can be achieved by exchanging the order of the two tables in left outer and right outer.
4. Full outer join = result of inner join + data in table 1 but not in table 2 + data in table 2 but not in table 1 (not supported by MySQL)

Import:
SELECT * FROM beauty;
SELECT * FROM boys; 

#Introduction: Query the names of goddesses whose boyfriends are not in the male god table SELECT b.NAME,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id; 

#Left outer join SELECT b.NAME,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;
#Select the primary key column from the table

#Right outer join, same result.
SELECT b.NAME,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL; 

#Change the boyfriend_id of row 10 in the beauty table from 4 to 6 to get the following results.
SELECT b.*,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.boyfriend_id=bo.id
WHERE b.id IS NULL; 

#Case 1. Which department has no employees.
#Left external writing method SELECT d.*,e.employee_id
FROM departments
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.department_id IS NULL;
==============================
#Right outer writing method SELECT d.*,e.employee_id
FROM employees
RIGHT OUTER JOIN departments d
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL; 

#The result of the full outer join consists of three parts (for example, Mysql does not support) Syntax structure demonstration First find the intersection of the two tables, then find the missing filling, and then find other unrelated ones Syntax structure example SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id=bo.id

#Cross join, Cartesian product implemented using the 1999 syntax standard SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo; 

#Comparison between SQL1992 syntax and SQL1999 syntax Function: SQL1999 supports more Readability: SQL1999 separates the connection conditions and the filter conditions, which improves readability

SELECT <select_list>
FROM A
INNER JOIN B
ON A.KEY=B.KEY; 

SELECT <select_list>
FROM A
LEFT JOIN B
ON A.KEY=B.KEY; 

SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.KEY=B.KEY; 

SELECT <select_list>
FROM A
LEFT JOIN B
ON A.KEY=B.KEY
WHERE B.KEY IS NULL; 

SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.KEY=B.KEY
WHERE A.KEY IS NULL; 

SELECT <select_list>
FROM A
FULL JOIN B
ON A.KEY=B.KEY; 

SELECT <select_list>
FROM A
FULL JOIN B
ON A.KEY=B.KEY
WHERE A.KEY IS NULL
OR B.KEY IS NULL;