1. Meaning

When fields of multiple tables are involved in a query, a join of multiple tables is required
Selectect field 1, field 2
from Table 1, Table 2,...;

Cartesian product: When querying multiple tables, no valid join condition is added, resulting in full join for all rows of multiple tables (Table 1 has m rows, Table 2 has n rows, result = m*n rows)
How to resolve: Add valid connection conditions

2. Classification

By age:
1,sql92
Only internal connections are supported
2. sql99 [Recommended]
Supports internal connections + external connections (left and right outer) + cross connections
By function:
Internal connections: equivalence, non-equivalence, self-connection
External connection: left outer connection, right outer connection, all outer connection
Cross Connection

3. SQL92 Syntax

1. Equivalent Connection

Grammar:
select query list
from Table Alias, Table 2 Alias
where Table 1.key=Table 2.key
[and filter conditions]
[group by grouping field]
[Screening after having grouping]
[order by sort field]
Characteristic:
(1) Aliasing tables in general
(2) The order of multiple tables can be changed
(3) n table joins require at least n-1 join conditions
(4) The result of equivalence joining is the intersection of multiple tables
Can be used with all clauses described earlier, such as sorting, grouping, filtering

#Case 1: Query goddess name and corresponding male god name
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`;

#2. Aliasing tables
/*
①Increase statement simplicity
②Distinguish fields with multiple duplicate names
 Note: If the table is aliased, the fields queried cannot be qualified with the original table name
*/
#Query employee name, type number, type name
SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

#3. The order of the two tables can be changed
#Query employee name, type number, type name
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;

#4. Can be filtered
#Case: Query employee name, department name with bonus
SELECT last_name,department_name,commission_pct
FROM employees e,departments 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 in the city name
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

#5. Grouping may be added
#Case 1: Query the number of departments per city
SELECT COUNT(*) Number,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

#Case 2: Query the Department name of each department with a bonus, the department's leadership number and the minimum wage for that department
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,d.`manager_id`;

#6. Can be sorted
#Case: Query the type name and number of employees for each job type, in descending order by number of employees
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#7. Three-table connection can be achieved
#Case: Query employee name, Department name, and 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`
AND city LIKE 's%'
ORDER BY department_name DESC;

2. Non-equivalent connections

Grammar:
select query list
from Table Alias, Table 2 Alias
where non-equivalent connection conditions
[and filter conditions]
[group by grouping field]
[Screening after having grouping]
[order by sort field]

#Case 1: Query the salary and salary level of an employee
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';

3. Self-connection

Grammar:
select query list
from table alias 1, table alias 2
where Equivalent Connection Conditions
[and filter conditions]
[group by grouping field]
[Screening after having grouping]
[order by sort field]

#Case: Query employee name and Supervisor Name
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`;

4. SQL99 Syntax

Separate connection and filter conditions for readability

1. Internal connection

Grammar:
select query list
FromTable 1 Alias
[inner] join table 2 alias on join condition
where filter condition
group by grouping list
Filtering after having grouped
order by sorted list
limit clause;
Characteristic:
1. The order of tables can be changed, sorting, grouping, filtering can be added, inner can omit
(2) Result of inner join = intersection of multiple tables
(3) n table joins require at least n-1 join conditions
(4) Place the filter conditions behind where and the connection conditions behind on to improve separation and ease of reading.
inner join join join has the same effect as equivalence join in sql92 syntax, both are intersections of query tables
Classification:
Equivalent connection, non-equivalent connection, self-connection
Equivalent Connection

#Case 1. Query employee name, department name
SELECT last_name,department_name
FROM departments d
 JOIN  employees e
ON e.`department_id` = d.`department_id`;

#Case 2. Query name with e for employee name and type 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%';

#Case 3.Query city names and departments with departments > 3, (add group + filter)
#1. Query the number of departments in each city
#(2) Select the ones that meet the criteria on the results of (1)
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 which department has more than 3 Department names and number of employees, in descending order by number (add sort)
#1. 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
#(2) Select records with more than 3 employees on the results and sort them
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, job type name, and 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;

Non-Equijoin

#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 wage levels > 20, in descending order by wage 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;

Self-connection

 #Query employee's name, 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 names of employees and supervisors whose names contain the character k
 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

Scenario: Used to query records in one table that are not in another
Grammar:
select query list
FromTable 1 Alias
left|right|full[outer]join Table 2 alias on join condition
where filter condition
group by grouping list
Filtering after having grouped
order by sorted list
limit clause;
Characteristic:
(1) The result of the query = all rows in the main table, matching rows will be displayed if the slave table matches it, and null will be displayed if there is no match from the table
(2) The left join is to the left of the main table, and the right join is to the right of the main table
full join has primary table on both sides
(3) Generally used to query unmatched rows except the intersection
(4) Total Outer Join = Inner Join Result + Table 1 + Table 2 not + Table 2 not

#Case: Query the name of a goddess whose boyfriend is not in the goddess table
 #Left Outer Connection
 SELECT b.*,bo.*
 FROM boys bo
 LEFT OUTER JOIN beauty b
 ON b.`boyfriend_id` = bo.`id`
 WHERE b.`id` IS NULL;
 
 #Case 1: Query which department has no employees
 #Outside Left
 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;
 #Outside Right
  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. Cross Connection

Grammar:
select query list
FromTable 1 Alias
cross join table 2 alias;
Characteristic:
Similar to Cartesian product

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

5. Comprehensive Cases

#1. Show the names, Department numbers and department names of all employees.
SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;

#2. Query job_id of department 90 employees and location_id of department 90
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;

#3. Select all employees with bonuses
SELECT last_name , department_name , l.location_id , city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;

#4. Select employees whose city works in Toronto
SELECT last_name , job_id , d.department_id , department_name 
FROM employees e,departments d ,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = 'Toronto';

#5. Query each type of work, department name of each department, type name and minimum wage
SELECT department_name,job_title,MIN(salary) minimum wage
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;

#6. Query country numbers with more than 2 departments per country
SELECT country_id,COUNT(*) Number of departments
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING Number of departments>2;

#7. Select the name, employee number, and manager's name and employee number of the specified employee, and the results are similar to the format below
employees	Emp#	manager	Mgr#
kochhar		101	king	100

SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name='kochhar';

#1. Query the goddess'boyfriend information with number > 3. If there is one, list it in detail. If not, fill it with null
SELECT b.id,b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id`>3;

#2. Query which city has no department
SELECT city
FROM departments d
RIGHT OUTER JOIN locations l 
ON d.`location_id`=l.`location_id`
WHERE  d.`department_id` IS NULL;

#3. Query employee information in department named SAL or IT
SELECT e.*,d.department_name,d.`department_id`
FROM departments  d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN('SAL','IT');