Connection query (this article only introduces the connection query of SQL 92 standard in detail)

1, Meaning and classification of connection query

• meaning:
  • Also known as multi table query, join query will be used when the query fields come from multiple tables
• Cartesian product phenomenon:
  • Suppose that table 1 has m rows, table 2 has n rows, and the result has m*n rows
• Cause of occurrence:
  • There are no valid connection conditions
• resolvent:
  • Add a valid connection condition
• Classification of connection conditions:
  • Classification by age:
    • SQL 92 standard: only internal connections are supported
    • SQL 99 standard: [recommended], which supports internal connection + external connection (excluding all external connections) + cross connection
  • Classification by function:
    • Internal connection: equivalent connection, non equivalent connection and self connection
    • External connection: left external connection, right external connection and full external connection
    • Cross connect

① The result of multi table equivalent connection is the intersection of multi tables
② N table connection, at least n-1 connection conditions are required
③ The order of multiple tables is not required, and aliases are generally used
④ You can use ORDER BY, GROUP BY, and WHERE filtering with all the clauses learned earlier

2, The equivalent connection (which can be understood as the intersection of two tables) 92 standard is the same below

Case 1: query the boys corresponding to girls

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

Case 2: query employee name and corresponding department name

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

Case 3 Query employee name, type of work number and type of work name

SELECT last_name,employees.job_id,job_title
FROM employees,jobs	#The order of multiple tables can be changed at will
WHERE employees.`job_id`=jobs.`job_id`;

Note: the table can be aliased, but after it is aliased, the original name will no longer be known. It needs to be represented by alias

SELECT e.last_name,e.job_id,job_title
FROM employees AS e,jobs j 
WHERE e.`job_id`=j.`job_id`;

1. Connection query AND filtering function: WHERE connection conditions AND filtering conditions

Case 4: query the employee name and department name with bonus

SELECT last_name,d.department_name,commission_pct
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;

Case 5: query the Department name and city name whose second character is o in the city name

SELECT department_name,city
FROM locations loca,departments depa
WHERE depa.`location_id` = loca.`location_id`
AND loca.`city` LIKE '_o%';

2. Connection query matching GROUP BY

Case 6: query the number of departments in each city

SELECT COUNT(*),city
FROM departments,locations
WHERE departments.`location_id`=locations.`location_id`
GROUP BY city;

Case 7: query the Department name and department leader number of each department with bonus and the minimum wage of the Department

SELECT department_name,e.`department_id`,d.manager_id, MIN(salary) min_salary
FROM departments d, employees e
WHERE e.`department_id` = d.`department_id`  #Connection condition: there is a department in the employees table_ id 	 Corresponds to the Department in the departments table_ id
AND e.`commission_pct` IS NOT NULL
GROUP BY e.`department_id`;

3. Connect multiple tables + grouping + sorting

Case 8: query the name of each type of work and the number of employees, and sort by the number of employees

SELECT job_title,COUNT(*) AS cnt
FROM jobs, employees e
WHERE e.`job_id` = jobs.`job_id`
GROUP BY job_title
ORDER BY cnt DESC;

Case 9 connection table 3: query the city where the employee name and department name are located (and require the city to start with s)

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

3, Non equivalent connection

It can be simply understood as: judge whether a field in one table is between n fields in another table

Case 10: query employee's salary and salary grade

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

4, Self connection

The so-called self connection is to connect yourself. It is the operation of both sides of the same table
Note: a table is usually given different aliases to distinguish it

Case 11: query the employee id and the employee name and employee id of the supervisor

SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e,employees m
WHERE e.`manager_id` = m.`employee_id`;