Inner connection and outer connection

The courseware comes from station b, Shangsi valley. Please move to station b for details
All note links

Inner join: merge the rows of multiple tables with the same column. The result set only contains the matching rows in the table

#The following is the inner connection of MySQL 92: the result set is the matching row
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id


#The internal connection, JOIN connection table and ON of MySQL 99 realize the connection relationship
SELECT employee_id, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id		#Connection relationship

External join: merge the rows of more than two tables with the same column. In the result set, in addition to the matching rows, the rows in the left and right tables that do not match the conditions in WHERE are queried

External connection classification: left external connection, right external connection, full external connection (both left and right)

Table relationship in the experiment:

Example:

Query the last of all employees_ name,department_ Name information

Analysis: when you see that all employees are queried and multiple tables are queried, you should pay attention to the external connection

First, explain the experimental environment: the courseware from Shang Silicon Valley, in which an employee in the employees table has no department_id, and the other employees (106) have it, so this is the external connection

Which table has less data, we hope to fill it so that the table length of the two tables is the same

#Syntax of SQL92 (not supported by MySQL)
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
#Syntax of SQL99
#Left outer connection
SELECT last_name, department_name
FROM employees e LEFT OUTER JOIN departments d	#Both OUTER and INNER can be omitted
ON e.department_id = d.department_id;

From Shang Silicon Valley courseware

Use of UNION

Using UNION, as the name suggests, is to combine sets, but it still distinguishes between UNION and UNION ALL

Where UNION is to take the intersection of two sets and remove duplicate records

UNION ALL is a pure intersection set and does not remove duplicate parts

UNION:

UNION ALL

If both methods can get correct results in multi table query, UNION ALL is recommended to avoid duplication and improve efficiency

Implementation of 7 kinds of SQL JOINS

Figure in the middle: inner connection

SELECT employee_id, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id		#Connection relationship

Figure above left: left outer connection

SELECT employee_id, department_name
FROM employees e LEFT OUTER JOIN departments d	#Both OUTER and INNER can be omitted
ON e.department_id = d.department_id;

Figure above right: right outer connection

SELECT employee_id, department_name
FROM employees e RIGHT OUTER JOIN departments d	#Both OUTER and INNER can be omitted
ON e.department_id = d.department_id;

Middle left: remove the middle part from the foundation of the left outer connection

In this example, the middle is department_ If the ID is not NULL, we use WHERE to filter once on the basis of determining the left outer connection, and only index the Department_ The ID is NULL

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

The middle right figure, as above, is the right outer connection, removing the middle repetition

SELECT employee_id, last_name, department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL

Bottom left, full external connection

MysQL doesn't support FULL JOIN 😦

1. Union all in the upper left and middle right (no weight removal, high efficiency)

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

UNION ALL  #Two tables are written separately, with UNION ALL in the middle

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

Figure at the bottom right: union outside the left and union outside the right

SELECT employee_id,last_name,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,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL