SQL connection query and SQL connection

SQL connection query and SQL connection
Brief Introduction

Briefly review and summarize the similarities and differences between different table join statements and some concepts.

The statement for creating a database is as follows:

1 drop database if exists 'Demo'; 2 create database 'Demo'; 3 USE 'Demo '; 4/* department TABLE */5 create table department (6 'id' int primary key AUTO_INCREMENT, 7 'name' VARCHAR (50) 8); 9 insert into department VALUES (DEFAULT, 'Finance '), (DEFAULT, 'Customer Service'), (DEFAULT, 'tech '); 10/* employee TABLE */11 create table employee (12 'id' int primary key AUTO_INCREMENT, 13 'name' VARCHAR (50), 14 'department _ id' INT, 15 CONSTRAINT fk_employee_department foreign key (id) REFERENCES department (id) 16); 17 insert into employee VALUES (DEFAULT, 'zhang san', 1), (DEFAULT, 'Li si ', 2), (DEFAULT, 'wang wu', 2 );

The code above shows that the Department table and employee table are created respectively. The employee table and department table are many-to-one, and the technology department has no employees.

Internal Connection

Syntax: inner join... on... inner can be omitted.

Concept: the result of an inner join query is to select data that meets the connection conditions from the combination of two or more tables. If the data cannot meet the connection conditions, ignore the data. In the inner join query, the joined tables are equal.

For example, to query employees and their departments, the SQL statement is as follows:

1 SELECT e. id, e. 'name', d. 'name' department 'FROM employee e JOIN department d ON e. 'department _ id' = d. 'id'

 

The result is as follows:

　　　　

It can be seen that the department column only queries departments with employees, while the technical department without employees does not.

External Connection

Concept: As mentioned above: In the inner join query, the tables involved in the join are equal. The tables that participate in the connection in the outer connection have the master-slave relationship. Match the data columns in the table from each row of the primary table, and directly return the data that meets the connection conditions to the result set. For columns that do not meet the connection conditions, return to the result set after a null value is entered.

Left Outer Join

Syntax: left outer join... on... outer can be omitted.

Concept: The result set of the left outer join query includes all rows in the left table, not just the matched rows. If a row in the left table does not match a row in the right table, all selected columns in the right table in the associated result set are null.

Example 1: Change the preceding connection statement to left Outer Join.

SELECT e. id, e. 'name', d. 'name' 'department 'FROM department d left join employee e ON e. 'department _ id' = d. 'id'

 

The query result is as follows:

　　　　

It can be seen that departments without employees are also queried, and the employee's id and name columns are filled with null values.

Example 2: query the number of employees in all departments. departments without employees should also be queried. The SQL statement is as follows:

1 SELECT d. 'name', COUNT (e. 'id') 'department count 'FROM department d left join employee e ON e. 'department _ id' = d. 'id' group by e. 'department _ id'

 

The result is as follows:

　　　　

It can be seen that no employee's technical department is also queried, and the number of people counting is 0 (ps: This 0 does not conflict with the previously automatically filled null value, because the aggregate function count () is used here, The result set is first filled with null, and then counted ).

Outer right connection

Syntax: right outer join... on... outer can be omitted.

Concept: the right outer join is similar to the left outer join, but the position changes. The result set must contain all rows in the right table. If some items in the right table are not in the left table, they are filled with null values.

Example 1: change the above left Outer Join statement to right outer join. The SQL statement is as follows:

1 SELECT d. 'name', COUNT (e. 'id') 'department count 'FROM employee e right join department d ON e. 'department _ id' = d. 'id' group by e. 'department _ id'

The result is as follows: