Detailed explanation of MySQL multi-table query examples [link query, subquery, etc.]

create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#Insert data into department values
(200,'Technology'),
(201,'Human Resources'),
(202, 'Sales'),
(203,'Operation');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('nvshen','male',18,200),
('xiaomage','female',18,204)
;

# View the table structure and data mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.19 sec)

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | Technology |
| 201 | Human Resources |
| 202 | Sales |
| 203 | Operations |
+------+--------------+
4 rows in set (0.02 sec)

mysql> select * from employee;
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | nvshen | male | 18 | 200 |
| 6 | xiaomage | female | 18 | 204 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)

mysql> select * from employee,department;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | technology |
| 1 | egon | male | 18 | 200 | 201 | Human Resources |
| 1 | egon | male | 18 | 200 | 202 | for sale |
| 1 | egon | male | 18 | 200 | 203 | operations |
| 2 | alex | female | 48 | 201 | 200 | technology |
| 2 | alex | female | 48 | 201 | 201 | human resources |
| 2 | alex | female | 48 | 201 | 202 | sales |
| 2 | alex | female | 48 | 201 | 203 | operations |
| 3 | wupeiqi | male | 38 | 201 | 200 | technology |
| 3 | wupeiqi | male | 38 | 201 | 201 | Human Resources |
| 3 | wupeiqi | male | 38 | 201 | 202 | sales |
| 3 | wupeiqi | male | 38 | 201 | 203 | Operations |
| 4 | yuanhao | female | 28 | 202 | 200 | Technology |
| 4 | yuanhao | female | 28 | 202 | 201 | Human Resources |
| 4 | yuanhao | female | 28 | 202 | 202 | Sales |
| 4 | yuanhao | female | 28 | 202 | 203 | Operations |
| 5 | nvshen | male | 18 | 200 | 200 | technology |
| 5 | nvshen | male | 18 | 200 | 201 | Human Resources |
| 5 | nvshen | male | 18 | 200 | 202 | sales |
| 5 | nvshen | male | 18 | 200 | 203 | Operations |
| 6 | xiaomage | female | 18 | 204 | 200 | technology |
| 6 | xiaomage | female | 18 | 204 | 201 | Human Resources |
| 6 | xiaomage | female | 18 | 204 | 202 | sales |
| 6 | xiaomage | female | 18 | 204 | 203 | Operations |

#Find the common parts of the two tables, which is equivalent to using the conditions to filter out the matching results from the Cartesian product results. #Department does not have department 204, so the employee information about employee 204 in the employee table is not matched.mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+---------+------+--------+--------------+
| id | name | age | sex | name |
+----+---------+------+--------+--------------+
| 1 | egon | 18 | male | technology |
| 2 | alex | 48 | female | human resources |
| 3 | wupeiqi | 38 | male | Human Resources |
| 4 | yuanhao | 28 | female | Sales |
| 5 | nvshen | 18 | male | technology |
+----+---------+------+--------+--------------+
5 rows in set (0.00 sec)

#The above sql is equivalent to mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

#Take the left table as the standard, that is, find all employee information, including employees without departments. #The essence is: add the results on the left but not on the right based on the inner joinmysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+----------+--------------+
| id | name | depart_name |
+----+----------+--------------+
| 1 | egon | technology |
| 5 | nvshen | Technology |
| 2 | alex | Human Resources |
| 3 | wupeiqi | Human Resources |
| 4 | yuanhao | Sales |
| 6 | xiaomage | NULL |
+----+----------+--------------+
6 rows in set (0.00 sec)

#Based on the right table, find all department information, including departments without employees. #The essence is: add the results on the right but not on the left based on the inner joinmysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+---------+--------------+
| id | name | depart_name |
+------+---------+--------------+
| 1 | egon | technology |
| 2 | alex | Human Resources |
| 3 | wupeiqi | Human Resources |
| 4 | yuanhao | Sales |
| 5 | nvshen | Technology |
| NULL | NULL | Operations |
+------+---------+--------------+
6 rows in set (0.00 sec)

 mysql> select * from employee left join department on employee.dep_id = department.id
     union
    select * from employee right join department on employee.dep_id = department.id
      ;
+------+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | technology |
| 5 | nvshen | male | 18 | 200 | 200 | technology |
| 2 | alex | female | 48 | 201 | 201 | human resources |
| 3 | wupeiqi | male | 38 | 201 | 201 | Human Resources |
| 4 | yuanhao | female | 28 | 202 | 202 | Sales |
| 6 | xiaomage | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | Operations |
+------+----------+--------+------+--------+------+--------------+
7 rows in set (0.01 sec)

#Note the difference between union and union all: union will remove identical records

select employee.name,department.name from employee inner join department
  on employee.dep_id = department.id
  where age > 25;

#Query the department names with an average age of over 25 years old select id,name from department
  where id in
    (select dep_id from employee group by dep_id having avg(age) > 25);
# View the names of technical department employees select name from employee
  where dep_id in
    (select id from department where name='Technology');
#View the department names with less than 1 person select name from department
  where id not in
    (select dep_id from employee group by dep_id);

#Comparison operators: =, !=, >, >=, <, <=, <>
#Query the name and age of employees who are older than the average age of all employeesmysql> select name,age from employee where age > (select avg(age) from employee);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
#Query the name and age of employees who are older than the average age in the department

mysql> select t1.name,t1.age from employee as t1
       inner join
      (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
      on t1.dep_id = t2.dep_id
      where t1.age > t2.avg_age;
+------+------+
| name | age |
+------+------+
| alex | 48 |

#EXISTS keyword indicates existence. When the EXISTS keyword is used, the inner query statement does not return the queried records. Instead, it returns a true or false value. True or False
#When True is returned, the outer query statement will query; when False is returned, the outer query statement will not query #department table has dept_id=203, True
mysql> select * from employee where exists (select id from department where id=200);
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | nvshen | male | 18 | 200 |
| 6 | xiaomage | female | 18 | 204 |
+----+----------+--------+------+--------+
#department table contains dept_id=205, False
mysql> select * from employee where exists (select id from department where id=204);
Empty set (0.00 sec)