Contents

JOIN query JOIN

Meaning

Cartesian product phenomenon

classification

By age

By function

I. sql92

1. Equivalent connection

1) Alias table

2) The order of two tables can be exchanged

3) Insert filter

4) Join group

5) Add sort

6) Realize the connection of three tables

2. Non equivalent connection

3. Self connect

Two, sql99

grammar

1. Internal connection

grammar

Characteristic

1) Change position

2) Add filter

3) Group + filter

4) Add sort

5) Three meter connection

2. Non equivalent connection

3. Self connect

4. External connection

Application scenario

Characteristic

1) Left outer connection

2) Right outer connection

3) Total external connection

3) Cross connect

Summarize sql92 V.S. sql99

For the configuration and installation of MySQL and sqliog involved in this article, please refer to MySQL configuration + sqllog installation tutorial 

JOIN query JOIN

Meaning

It is also called multi table query. When the fields of query come from multiple tables, join query will be used

Cartesian product phenomenon

Table 1 has row m, table 2 has row n, the result is row m*n
Cause: no valid connection conditions
How to avoid: adding valid link conditions

classification

By age

sql92 standard: only internal connection is supported [in mysql]

sql99 standard [recommended]: support inner connection + outer connection (left outer + right outer) + cross connection [in mysql]

By function

Internal connection

• Equivalent connection
• Non equivalent connection
• Self join

External connection

• Left outer join
• Right outer join
• All external connection

Cross connect

I. sql92

1. Equivalent connection

Characteristic

• The result of isojoin of multiple tables is the intersection part of multiple tables
• N table connection, at least n-1 connection conditions are required
• No requirement for multi table sequence
• Generally, alias is needed for the table
• All the previous query clauses can be cultured, such as sorting, grouping and filtering

#Case 1: query girls' names and corresponding boys' names
SELECT NAME, boyName
FROM boys, beauty
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`;

1) Alias table

• Improve the conciseness of statements
• Distinguish multiple fields with duplicate names
• If the alias is set for the table, the query field cannot be qualified with the original table name

#Query employee number, work type number and work type name
SELECT last_name, e.job_id, job_title
FROM employees AS e, jobs
WHERE e.`job_id`=jobs.`job_id`;

2) The order of two tables can be exchanged

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

3) Insert filter

#Case 1: name of employee and department with bonus
SELECT last_name, department_name, commission_pct
FROM employees AS e, departments AS d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

#Case 2: query the Department name and city name whose second character is o
SELECT department_name, city
FROM departments AS d, locations AS l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';

4) Join group

#Case 1: query the number of departments in each city
SELECT COUNT(*), city
FROM departments AS d, locations AS l
WHERE d.location_id=l.location_id
GROUP BY city;

#Case 2: find out the Department name, department leader number and minimum wage of each department with bonus
SELECT department_name, d.manager_id, MIN(salary)
FROM departments d, employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name, manager_id;

5) Add sort

#Case: query the name of each type of work and the number of employees in descending order
SELECT COUNT(*), job_title, j.job_id
FROM employees e, jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

6) Realize the connection of three tables

#Case: query employee name, department name, city
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`;

2. Non equivalent connection

Create a new job grades 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);
*/

#Case 1: querying employees' wages and wage levels
SELECT salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A'; #You can add these

3. Self connect

#Case: query the employee name and the name of its superior
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`;

Two, sql99

grammar

select query list
 from table 1 alias [connection type (internal, external, cross)]
join table 2 alias  
on connection conditions
 [where screening criteria]
[group by]
[screening after having group]
order by

inner connection: inner

External connection:

• Left outside: left [outer] can omit the outer in []
• right [outer]
• full [outer]

cross connect: cross

1. Internal connection

grammar

select query list
 from table 1 alias 
inner join table 2 alias
 on connection conditions;

Characteristic

• You can add sorting, grouping, filtering
• inner can be omitted
• Filter conditions are placed after where and join conditions are placed after on to improve separation and facilitate reading
• The inner join join is the same as the equivalent join in sql92, which is the intersection of multiple tables

1) Change position

#Case 1: query employee name and department name (exchange location)
SELECT last_name, department_name
FROM employees e #The exchange order of two tables is also OK
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;

2) Add filter

#Case 2: query the employee name and work type name with e in the name (add filter)
SELECT last_name, e.job_id
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE '%e%';

3) Group + filter

#Case 3: query city name and number of departments with more than 3 departments (grouping + filtering)
SELECT COUNT(*) Number of departments, city
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

4) Add sort

#Case 4: query the Department name and the number of employees whose number of department employees is greater than 3, and sort them in descending order
SELECT COUNT(*), department_name
FROM employees s
INNER JOIN departments d
ON s.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

5) Three meter connection

#Case 5: query employee name, department name, work type name, and descending by department name (connected by three tables)
SELECT last_name, department_name, job_title
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 department_name DESC;

2. Non equivalent connection

#Case 1: query the salary level of employees
SELECT salary, grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#Case 2: query the number of each salary level greater than 20, and sort by salary level descending order
SELECT COUNT(*), grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

3. Self connect

#Case: query the name of the employee whose name contains k and the name of the superior
SELECT e.last_name, m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`
WHERE e.last_name LIKE '%k%';

4. External connection

Application scenario

Used to query records in one table and not in another

Characteristic

• The query results of the external connection are all records in the main table: if there is a match in the slave table, it will be displayed; if there is no match in the slave table, it will be null; the query results of the external connection = the results of the internal connection + the records in the main table but not in the slave table
• Left outer join, left left main table
• right is the main table
• If you exchange the order of two tables, you can achieve the same effect
• Total external connection = internal connection result + table 1 has but Table 2 does not + table 2 has but Table 1 does not (if you want to remove one of them, you need to use where b.id = null)

1) Left outer connection

#Case 1: query the names of girls whose boyfriends are not in the boys' table
SELECT b.name, bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

#Case 2: query which department has no employees
SELECT d.*, e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

2) Right outer connection

#Case 1: query the names of girls whose boyfriends are not in the boys' table
SELECT b.name, bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

#Case 2: query which department has no employees
SELECT d.*, e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

3) Total external connection

use girls;
select b.*, bo.*
from beauty b
full outer join boys bo
on b.boyfriend_id = bo.id
#[three part composition] find out the intersection part. Some of the slave tables in the main table do not show null (equivalent to the outside left), some of the slave tables do not show null (equivalent to the outside left), and some of the slave tables do not

3) Cross connect

Equivalent to Cartesian product

SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo;

Summarize sql92 V.S. sql99

Function: sql99 supports more

Readability: sql99 realizes the separation of connection condition and filter condition, with high readability

Set A, B

A cross B: inner join

A+A intersection B: A left join B

B: A right join B

A-A cross B: A left join plus where condition B.key is null

B-A intersection B: A right join plus where condition A.key is null

A+B: A full join B on A.key=B.key

A+B-A intersection B: A full join B on A.key=B.key where A.key is null or B.key is null