Join query in sql99 syntax

• Syntax:

SELECT query list
FROM table 1 alias
[connection type] JOIN table 2 alias ON connection conditions
[WHERE filter criteria]
[GROUP BY]
[HAVING filter criteria]
[ORDER BY sort list]

• Connection type:

inner connection: inner
External connection:
Left outer: left [outer]
Right outer: right [outer]
full [outer]
Cross connect:
cross

1, Inner connection

• Syntax:

SELECT query list
FROM table 1 alias
INNER JOIN table 2 aliases
ON connection condition
​ ...

• to subdivide:

Equivalent connection non equivalent connection self connection

• characteristic:

① You can add sorting, grouping and filtering
② inner can be ignored
③ The filter condition is after where and the connection condition is after on, which improves the separation and is easy to read
④ The effect of inner join connection is the same as that of equivalent connection in sql 92 syntax, which is the intersection of multiple tables

1. Equivalent connection

Case 1 Query employee name and department name

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

After changing the order, the effect is equivalent to case 1

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

Case 2 The query name contains the employee name and type of work name of e (add filter)

SELECT last_name, 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 name and number of departments with more than 3 departments (add filtering and grouping)

SELECT city, COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

Case 4 Query the name and number of employees of departments with more than 3 employees, and sort by number

SELECT department_name, COUNT(*) AS Number of employees
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_name`
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

Case 5 Query employee name, department name and type of work name in descending order according to department name

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 d.department_name DESC;

2. Non equivalent connection

Case 6 Query employee's salary level

SELECT COUNT(*),grade_level
FROM employees e
INNER JOIN job_grades j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY grade_level
HAVING	COUNT(*) > 20
ORDER BY grade_level DESC;

3. Self connection

Case 7 Query the name of the employee whose name contains the character k and the name of the superior

SELECT e.last_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

2, External connection

1. External connection query results are all records in the main table
If there is a matching from the table, the matching value is displayed
If there is no match from the table, null is displayed
External connection query result = internal connection query result + records in the master table but not in the slave table

2. Left outer join: the left side of the left join is the main table
Right outer join: the main table is on the right of right join

3. The same effect can be achieved by exchanging the order of two tables outside the left and right

Left outer connection and right outer connection

Case 8 Query the name of the girl whose boyfriend is not in the boy's table

SELECT NAME
FROM beauty g
LEFT OUTER JOIN boys bo
ON g.`boyfriend_id` = bo.`id`   #During the matching process, if it cannot be matched, it will be set to null
WHERE bo.`id` IS NULL;			#It is best to use the primary key for filtering criteria, because the primary key must not be empty in the original slave table
								#If there is null, it must be the result of mismatching

• Use the right outer connection to realize the requirements of case 8 above

SELECT NAME
FROM boys bo
RIGHT OUTER JOIN beauty g
ON bo.`id` = g.`boyfriend_id`
WHERE bo.`id` IS  NULL;

Case 9: query which department has no employees (left outer connection)

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

• The right external connection realizes the requirements of case 9 above

SELECT d.department_id, department_name, 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

• characteristic:

Suppose there are tables 1 and 2

The total external connection matching result consists of three parts:
① Table 1 Table 2 parts that can be matched with each other (intersection of both)
② As the main table, table 1 matches the parts of Table 2 that are not included in Table 2 (Table 1 has but Table 2 does not)
③ Table 2 is used as the main table to match the parts of Table 1 that are not in Table 1 (Table 2 has but Table 1 does not)
Because of the above symmetry, the sequence of tables 1 and 2 can be changed at will

4, Cross connect

• characteristic:

The essence is to realize the Cartesian product
If Table 1 has m rows and table 2 has n rows, the result is m*n rows

memory:

In sql92, Cartesian product will occur when there is no suitable connection condition
In sql99, the phenomenon of Cartesian product in SQL is realized by cross connection syntax

SELECT * FROM beauty; 	#12 lines
SELECT * FROM boys;		#4 lines

SELECT b.*,g.*
FROM beauty g
CROSS JOIN boys b;		#The result is 48 lines