1, Grammar

select Query list
	from Table 1 alias [connection type]
	join Table 2 aliases 
	on Connection conditions
	[where [filter criteria]
	[group by [grouping]
	[having [filter criteria]
	[order by Sort list]

Classification:
inner connection (★): inner
External connection
Left outer (★): left [outer]
Right outer (★): right [outer]
full [outer]
cross connect: cross

2, Classification

1. Inner connection

Syntax:

select Query list
from Table 1 aliases
inner join Table 2 aliases
on Connection conditions;

Classification:
equivalence
non-equivalence
Self connection

characteristic:
① Add sorting, grouping, filtering
② inner can be omitted
③ The filter conditions are placed after where and the connection conditions are placed after on to improve the separation and facilitate reading
④ The effect of inner join connection is the same as that of equivalent connection in sql92 syntax, which is the intersection of query multiple tables

(1) Equivalent connection

#Case 1. Query employee name and department name

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

#Case 2. Query the employee name and type of work included e in the name (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%';

#3. Query the city name and department number with department number > 3, (add grouping + filter)

#① Query the number of departments in each city
#② ① select the qualified ones on the results
SELECT city,COUNT(*) Number of departments
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

#Case 4. Query the Department name and number of employees in which department the number of employees > 3, and sort by the number in descending order (add sort)

#① Query the number of employees in each department
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name

#② ① filter and sort the records with more than 3 employees on the results

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

#5. Query employee name, department name and type of work name in descending order by department name (add three table connection)

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

#Query employee's salary level

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#Query the number of salary levels > 20, in descending order by salary level

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 connection

#Query employee's name and superior's name

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

#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
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`
 WHERE e.`last_name` LIKE '%k%';

2. External connection

**Application scenario: * * used to query records in one table but not in another

characteristic:
1. The query result of external connection is all records in the main table
If there is a matching from the table, the matching value is displayed
If there is no matching from the table, null is displayed
External connection query result = internal connection result + records in the master table but not in the slave table
2. Left outer join, left join, left 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 the two tables outside the left and right
4. Total external connection = results of internal connection + those in Table 1 but not in Table 2 + those in Table 2 but not in Table 1

#Case 1: query which department has no employees

#Left outer
 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;
 
 
 #Right outer
 
  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;