MySQL multi-table query detailed explanation

SELECT * FROM boys; 

SELECT * FROM beauty; 

SELECT NAME,boyname FROM boys,beauty;
Final result: 12*4=48 rows 

#Advanced 6: Join query Meaning: Also known as multi-table query, when the query fields come from multiple tables, join query is used Cartesian product phenomenon: Table 1 has m rows, Table 2 has n rows, result = m*n rows Cause: No valid join condition Solution: Add valid join condition Join category:
   By era:
   SQL1992 standard (192 standard): only supports inner joins SQL1999 standard (199 standard) [Recommendation]: supports inner joins + outer joins (left outer and right outer) + cross joins Classification by function:
   Inner join:
     Equi-join Non-equi-join Self-join Outer join:
     Left outer join right outer join full outer join cross join:
      Left outer join right outer join full outer join cross join:

SELECT NAME,boyname FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id; 

#I. SQL192 Standard #1. Equi-join ① The result of multi-table equi-join is the intersection of multiple tables ② For n-table join, at least n-1 join conditions are required ③ There is no requirement for the order of multiple tables ④ Generally, an alias is required for the table ⑤ It can be used with all the clauses introduced above, such as sorting, grouping, and filtering.
#Multi-table query, match first and then filter#Case 1. Query employee name and corresponding department name.
SELECT first_name AS name,department_name AS department_name FROM employees,departments
WHERE employees.department_id = departments.department_id; 

#Case 2. Query employee name, job number, and job name. For fields shared by two tables, you need to add the table name as a limit, otherwise an error will be reported.
Error example:
SELECT first_name AS name,employees.job_id AS job number,job_title AS job name FROM employees,jobs
WHERE employees.job_id = jobs.job_id;

#2. Alias ​​the table ① Improve the simplicity of the statement ② Distinguish multiple renamed fields Note: If you give the table an alias, the query field cannot be limited by the original table name SELECT first_name AS name, e.job_id AS job number, job_title AS job name FROM employees AS e, jobs AS j
WHERE e.job_id = j.job_id; 

#3. Can the order of the two table names be swapped? Yes, they can.
SELECT first_name AS name, e.job_id AS job number, job_title AS job nameFROM jobs AS j, employees AS e
WHERE e.job_id = j.job_id;

#4. You can add filters#Case 3. Query the names of employees and departments who have bonuses.
SELECT first_name AS name,department_name AS department_name,commission_pct AS bonus FROM employees AS e,departments AS d
WHERE e.department_id=d.department_id
AND commission_pct IS NOT NULL; #AND e.commission_pct IS NOT NULL; 

#Case 4. Query the city name and department name corresponding to the second character of the city name being o.
SELECT city AS city,department_name AS department_name FROM locations AS l,departments AS d
WHERE l.location_id = d.location_id
AND city LIKE '_o%'; 

#5. You can add groups#Case 1. Query the number of departments in each city.
SELECT city AS city,COUNT(department_id) AS numberFROM locations AS l,departments AS d
WHERE l.location_id = d.location_id
GROUP BY l.city; 

#Case 2. Query the department name, leader number and minimum salary of each department with bonus.
#Don't be sure to add both columns when querying.
SELECT commission_pct AS bonus, department_name AS department name,
d.manager_id AS leadership number,MIN(salary) AS minimum salary FROM employees AS e,departments AS d
WHERE e.department_id = d.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id; 

#6. You can add sorting#Case 1: Query the job name and number of employees for each job type, and sort by the number of employees in descending order.
SELECT j.job_title AS job name,COUNT(employee_id) AS numberFROM employees AS e,jobs AS j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY number DESC; 

#7. Three-table join #Case 1. Query employee name, department name and city SELECT first_name AS name, d.manager_id AS department name, city AS city FROM employees AS e, departments AS d, locations AS l
WHERE e.department_id = d.department_id
 AND d.location_id = l.location_id; 

#Case 2. Query employee name, department name and city, where the city starts with s.
SELECT first_name AS name, d.manager_id AS department name, city AS city FROM employees AS e, departments AS d, locations AS l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
 AND city LIKE 's%'; 

#Case 3. Query employee name, department name and city, where the city starts with s, and sort by name in descending order.
SELECT first_name AS name, d.manager_id AS department name, city AS city FROM employees AS e, departments AS d, locations AS l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city LIKE 's%'
ORDER BY department_name DESC; 

Add a new salary grade table.
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal int,
 highest_sal int);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000); 

#2. Non-equivalued join, (range judgment)
#Case 1. Query employees' salaries and salary levels.
SELECT salary AS salary, grade_level AS grade FROM employees AS e, job_grades AS g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal; 

#Case 2. Query employees’ salaries and salary levels, and display employees at level A.
SELECT salary AS salary, grade_level AS grade FROM employees AS e, job_grades AS g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level = 'A'; 

#3. Self-connection [connecting to itself] 

#Case 1. Query the employee name and the name of the supervisor.
SELECT e.employee_id,e.last_name AS employee,
m.employee_id,m.last_name AS leader FROM employees e,employees m
WHERE e.manager_id = m.employee_id;