Meaning: it is also called multi table query. When the query fields come from multiple tables, the join query will be used

Cartesian boarding phenomenon: Table 1 has n rows, table 2 has m rows, and the result = n*m rows

Cause: there is no valid connection condition

How to avoid: adding valid connection conditions

Classification: 1. Classification by age: SQL92 standard (only supports internal connection), SQL99 standard [recommended (supports internal connection + external connection (left outer + right outer) + cross connection)

2. Classified by function: internal connection, external connection and cross connection

Internal connection: equivalent connection, non equivalent connection and self connection

External connection: left external connection, right external connection and full external connection

1, SQL92 standard

1. Equivalent connection

The result of multi table equivalent connection is the intersection of multi tables

N table connection, at least n-1 connection conditions are required

The order of multiple tables is not required

Aliasing tables is generally required

It can be used with all the clauses described above, such as sorting, grouping and filtering

#Case 1: query the goddess name and the corresponding male god name
use girls;
select name, boyName from boys, beauty where beauty.boyfriend_id = boys.id;
#Case 2: query employee name and corresponding department name
use myemployees;
select last_name, department_name from employees, departments where employees.department_id = departments.department_id;
#(1) Alias the table (the alias is relatively short: improve brevity and distinguish multiple fields with duplicate names)
#Note: when aliases are used, all aliases are used. Because the from is executed first, the name of the table has become new and the original table name is not recognized
#Case 1: query employee name, type of work number and type of work
select e.last_name, e.job_id, job_title from employees as e, jobs as j where e.job_id = j.job_id;
#(2) The order of the two tables can be interchanged, and the effect remains the same
select e.last_name, e.job_id, job_title from jobs as j, employees as e where e.job_id = j.job_id;
#(3) Can I add screening criteria?
#Case 2: query employee name and department name with bonus
select last_name, department_name from employees e, departments d where e.department_id = d.department_id and e.commission_pct is not null;
#Case 3: query the Department name and city name corresponding to the second character 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%';
#(4) Can you add groups?
#Case 4: query the number of departments in each city
select count(*) number, city from departments d, locations l where d.location_id = l.location_id group by city;
#Case 5: query the Department name and department leader number of each department with bonus and the minimum wage of the 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;
#(5) Can you add sorting?
#Case 6: query the name of each type of work and the number of employees in descending order by the 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;
#(6) Can I connect three meters?
#Case 7: 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;
#Case 8: query employee name, department name and city, and the city starts with s
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%';

2. Non equivalent connection

#Case 1: query employee's salary and salary level
select salary, grade_level from employees e, job_grades g where salary between g.lowest_sal and g.highest_sal;
#Case 2: query employee's salary and salary level = A
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

#Case 1: query the employee name and the name of his leader
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;

2, SQL99 standard

Statement: select query list from table 1 alias [connection type] join table 2 alias on connection conditions [where filter conditions] [group by grouping] [having filter conditions] [order by sort list]

Classification: inner connection: Inner

External connection: left [outer]

right [outer]

full [outer]

cross connect: cross

1. Inner connection

Syntax: select query list from table 1 alias inner join table 2 alias on join condition

Classification: equivalent connection, non equivalent connection and self connection

Features: sorting, grouping and filtering can be added;

inner can be omitted;

Put the screening conditions behind where to improve the separation and facilitate reading;

The effect of inner join connection is the same as that of equivalent connection in SQL92 syntax

(1) Equivalent connection

#Case 1: query employee name and department name (the exchange position does not affect)
select last_name, department_name from employees e inner join departments d on e.department_id = d.department_id;
select last_name, department_name from departments d inner 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 criteria)
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 grouping and filter criteria)
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 the number of employees is greater than 3, and sort them in descending order according to the number of employees (add sorting)
select count(*), 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;
#Case 5: query employee name, department name and type of work name in descending order by department name (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

#Case 1: query employee's salary level
select salary, grade_level from employees e inner join job_grades g on e.salary between g.lowest_sal and g.highest_sal;
#Case 2: query the number of wage levels greater than 20 and in descending order by wage level
select count(*), grade_level from employees e inner 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

#Case 1: query the employee's name and his superior's name
select e.last_name, m.last_name from employees e join employees m on e.manager_id = m.employee_id;
#Case 2: query the employee name including k's employee name and his superior's name
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 that exist in one table but not in another

Features: 1. The query result of the external connection is all records in the master table. If there is a matching value in the slave table, the matching value will be displayed. If there is no matching value, null will be displayed. The query result of the external connection = internal connection 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: right join the right side is the main table

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 the name of a girl whose boyfriend is not in the male god table
use girls;
#Left outer connection:
select b.name from beauty b left outer join boys bo on b.boyfriend_id = bo.id where bo.id is null;
#Right outer connection:
select b.name 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
use myemployees;
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;
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 connect

use girls;
select  b.*, bo.* from beauty b cross join boys bo;

SQL92 and SQL99 PK:

Functionally: SQL99 supports many

Readability: SQL99 realizes the separation of connection conditions and filter conditions, with high readability