test data

CREATE TABLE dept( /*Department table*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');




#Create EMP employee table
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*number*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*name*/
job VARCHAR(9) NOT NULL DEFAULT "",/*work*/
mgr MEDIUMINT UNSIGNED ,/*Superior number*/
hiredate DATE NOT NULL,/*Entry time*/
sal DECIMAL(7,2)  NOT NULL,/*salary*/
comm DECIMAL(7,2) ,/*dividend*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*Department number*/
);

 
 INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);

Link query

• Link query: query each employee and their department name (Cartesian product phenomenon)

select *
from emp,dept
order by emp.empno;

Query result diagram:

There are 14 records in emp table and 14 records in dept table. We find out the information and department name of each employee, and an employee can only belong to one department. Therefore, normally, 14 records should be returned. 56 records are returned here because we did not add link conditions, resulting in Cartesian product phenomenon, that is, each record in the first table is linked with each record in the second chapter table. If the first record, smith's department number is 20, but this record is linked with department numbers 10, 20, 30 and 40.

• Filter records by equivalent link conditions (the same record is filtered by conditions)

At this time, you can solve the problem of redundant records by adding link conditions.

select *
from emp,dept
where emp.deptno =dept.deptno
order by emp.empno;

At this point, the result set is normal. This link condition indicates that the value of the deptno field of the emp table must be equal to the value of the deptno field of the dept table before it can be filtered out.
For example, if the deptno of the first record EMP is 20 and the deptno of the dept table is 40, then the record is gone. If we look at the second record, the deptno of the EMP table is 20 and the deptno of the dept table is 30, then there is no record. If we look at the third record, the deptno of the EMP table is 20 and the deptno of the dept table is 20, which meets our conditions, Then the record will be returned, and so on, the record of emp.deptno = dept.deptno will be returned.

Union and union all

• union all combines the results of multiple sql statements
  For example, query employees whose employee number is greater than 7600 or whose salary is greater than 2000
  An sql can be written like this

select *
from emp
where sal > 2000 or empno > 7600

There are 17 records in total
If you use union all, you can divide this or condition into two sql statements

select *
from emp
where empno > 7600
union
select *
from emp
where sal > 2000

The query effect is the same as the sql above.

• union all
  The difference between union all and union is that union all does not duplicate the result set.

External connection query

• Why is there an external connection?
  Take the above employee and department cases. When I query each employee's Department, I can only find the employee information and department information of the Department. If the deptno of an employee is null, this record will not be found at this time. If you want to query the information of each employee and his department at this time, you can also query the information of no employees. Then you need an external connection.

select e.*,d.*
from emp e
left join dept d on e.deptno = d.deptno

Using the external connection, you can query the employee information with and without departments. The fields of the Department table of employees without departments are filled with null.

• Based on this, you can also query which departments have no employees?
  At this time, the Department table becomes the master table and the employee table becomes the slave table

select d.dname
from dept d
left join emp e on e.deptno = d.deptno
where e.empno is null

After associated query, if the primary key of the slave table is null, the Department without employees can be queried, because if the slave table is not connected with the condition of the master table, all fields are filled with null.