MySQL (eight) DQL connection query

First, meaning

Also multiprocessing table query, when the query field from more than one table, you will use the connection query.

II. Classification 2.1, chronological classification:

SQL92 Standard: Supports only internal connections

SQL99 standard "recommended": Support for internal connections + external connections (left and right outside) + cross Connect

2.2, by Function classification:

Internal connection: Equivalent connection, non-equivalent connection, self-connection

Outer connection: Left outer connection, right outer connection, full outer connection

Cross Connect

Third, SQL92 standard 3.1, equivalent connection:

The result of ① multiple table equivalent joins is the intersection part of multiple tables

②n table connection, at least n-1 connection conditions required

③ the order of multiple tables is not required

④ generally need to alias a table

⑤ can be used with all the clauses described earlier, such as sorting, grouping, filtering

(1) Simple query

Case: Query the employee name and the corresponding department name

SELECT Last_name,department_name from employees,departments WHERE employees. ' department_id ' =departments. ' Department _id ';

(2) Alias for table

① improve the simplicity of the statement

② distinguish multiple fields with duplicate names

Note: If you alias the table, the queried field cannot use the original table name to qualify

Case: Query employee name, job number, job 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 two tables can be swapped

(4) can be added to filter

Case: Search for 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;

(5) can be added in groups

Case: Query the number of departments in each city

The number of SELECT COUNT (*), City from departments d,locations l WHERE D. ' location_id ' =l. ' location_id ' GROUP by city;

(6) can be added to sort

Case: Query the number of job names and employees in each job, and descending 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 realized

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 ' ORDER by Department_name DESC;

3.2, non-equivalent connection

SELECT Salary,grade_level from Employees e,job_grades G WHERE salary between G. ' Lowest_sal ' and g. ' Highest_sal ';

3.3. Self-connected

Case: Querying employee names and superiors

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 ';

Four, sql99 syntax 4.1, Syntax:

Select query list from table 1 alias "Connection type" join table 2 alias on join condition "where filter Condition" "Group by Group" "Have Filter" "ORDER by Sort List"

4.2. Classification:

Inner Connection (★): Inner

External connection

Left Outside (★): "Outer"

Right outside (★): "Outer"

Full out: "outer" (MySQL not supported)

Cross Connect: Crosses

4.3, internal connection (1) Syntax:

Select query list from table 1 alias inner join table 2 alias on join condition;

(2) Classification:

Equivalent, non-equivalent, self-connected

(3) Features:

① adding sorting, grouping, filtering

②inner can be omitted

③ filter conditions are placed behind where the join conditions are on, improving separation and readability

The equivalent join effect in the ④inner join join and SQL92 syntax is the same as the intersection of querying multiple tables

(4) Equivalent connection

Case 1. Query Employee name, department name

Case 2: Name of employee and job name with E in the query (add filter)

Case 3. " Query Employee name, department name, job name, and descending by department name (add three table connections)

(5) Non-equivalent connection

Case 1, query the salary scale of employees

(6) Self-connected

Case 1, query the name of the employee, the name of the superior

4.4. External connection

(1) Application scenario: Used to query a table that has, another table does not have a record

(2) Features:

1, the outer join of the query results in all records in the table, if there is a match with it from the table, the matching value is displayed, if it does not match it from the table, then null is displayed; outer JOIN query result = INNER JOIN result + records from table not found in main table

2, left outer connection, left join is the main table, right outer connection, right join is the main table

3, left and right diplomacy for two table order, can achieve the same effect

4, the whole outer connection = The result of the Inner Connection + table 1 but table 2 does not have the + table 2 but table 1 does not have

(3) Find out which department has no employees

Left outer

SELECT d.*,e.employee_id from Departments D left OUTER joins employees E on D. ' department_id ' = E. ' department_id ' WHERE e. ' employee_id ' is NULL;

Right outside

SELECT d.*,e.employee_id from Employees e right OUTER JOIN departments d. ' department_id ' = E. ' department_id ' WHERE e. ' employee_id ' is NULL;

4.5. Cross-Connect

Syntax: Select query list from table 1 alias cross join table 2 alias;

Features: Similar to the Cartesian product (table 1 has 5 records, table 2 has 6 records, the result is 30 records)

Follow the public number: Java Back-end life, dry article first time delivery!

MySQL (eight) DQL connection query