How to Use subqueries in MySQL notes

A subquery is a nested query statement in another query statement.

Query results of the inner query statement, which can provide query conditions for the outer query statement

In certain circumstances, the conditions of one query statement must be obtained by another query statement.

Reference Table: employee

Reference Table: department

Subquery with IN keyword
Copy codeThe Code is as follows: mysql> SELECT * FROM employee
-> WHERE d_id IN
-> (SELECT d_id FROM department );
+ ------ + -------- + ------ + -------------------- +
| Num | d_id | name | age | sex | homeaddr |
+ ------ + -------- + ------ + -------------------- +
| 1 | 1001 | Zhang San | 26 | male | Haidian District, Beijing |
| 2 | 1001 | Li Si | 24 | female | Changping District, Beijing |
| 3 | 1002 | Wang Wu | 25 | male | Changsha city, Hunan Province |
+ ------ + -------- + ------ + -------------------- +
Rows in set (0.00 sec)

Query the information of all the d_id fields in the department table and use the result as a condition.

Query the information of all fields with the d_id condition in the employee table.

The not in effect is the opposite of the above

Subqueries with comparison Operators

Copy codeThe Code is as follows: mysql> SELECT d_id, d_name FROM department
-> WHERE d_id! =
-> (SELECT d_id FROM employee WHERE age = 24 );
+ ------ + ----------- +
| D_id | d_name |
+ ------ + ----------- +
| 1002 | Production Department |
| 1003 | sales department |
+ ------ + ----------- +
Rows in set (0.00 sec)

This shows which departments are not 24-year-olds, and it looks a little complicated.

In addition, there are many operators, so we will not repeat them here

Subquery with EXISTS keyword

EXISTS keyword indicates existence. When the EXISTS keyword is used, the inner query statement returns a true value instead of a query record. If the inner query statement queries a record that meets the conditions, true is returned. Otherwise, false is returned.

If the returned value is true, the outer query statement is used for query. Otherwise, no query is performed.

Copy codeThe Code is as follows: mysql> SELECT * FROM employee
-> WHERE EXISTS
-> (SELECT d_name FROM department WHERE d_id = 1004 );
Empty set (0.00 sec)

The inner loop does not query the results that meet the conditions. Therefore, false is returned and the outer query is not executed.

Not exists is the opposite

Of course, the EXISTS keyword can be used with other query conditions.

Use and or to connect conditional expressions with EXISTS keywords

Copy codeThe Code is as follows: mysql> SELECT * FROM employee
-> WHERE age> 24 AND EXISTS
-> (SELECT d_name FROM department WHERE d_id = 1003 );
+ ------ + -------- + ------ + -------------------- +
| Num | d_id | name | age | sex | homeaddr |
+ ------ + -------- + ------ + -------------------- +
| 1 | 1001 | Zhang San | 26 | male | Haidian District, Beijing |
| 3 | 1002 | Wang Wu | 25 | male | Changsha city, Hunan Province |
+ ------ + -------- + ------ + -------------------- +
Rows in set (0.00 sec)

Subquery with ANY keyword

The ANY keyword indicates that ANY of the conditions is met.

Copy codeThe Code is as follows: mysql> SELECT * FROM employee
-> WHERE d_id! = ANY
-> (SELECT d_id FROM department );
+ ------ + -------- + ------ + -------------------- +
| Num | d_id | name | age | sex | homeaddr |
+ ------ + -------- + ------ + -------------------- +
| 1 | 1001 | Zhang San | 26 | male | Haidian District, Beijing |
| 2 | 1001 | Li Si | 24 | female | Changping District, Beijing |
| 3 | 1002 | Wang Wu | 25 | male | Changsha city, Hunan Province |
| 4 | 1004 | Aric | 15 | male | engand |
+ ------ + -------- + ------ + -------------------- +
Rows in set (0.00 sec)

Subquery with the ALL keyword

The ALL keyword indicates that ALL conditions are satisfied.

Copy codeThe Code is as follows: mysql> SELECT * FROM employee
-> WHERE d_id> = ALL
-> (SELECT d_id FROM department );
+ ------ + ---------- +
| Num | d_id | name | age | sex | homeaddr |
+ ------ + ---------- +
| 4 | 1004 | Aric | 15 | male | engand |
+ ------ + ---------- +
Row in set (0.00 sec)

I do not understand what these two statements mean for the moment. I will try again later.

UNION merge query results

Copy codeThe Code is as follows: mysql> SELECT d_id FROM employee
-> UNION
-> SELECT d_id FROM department;
+ ------ +
| D_id |
+ ------ +
| 1, 1001 |
| 1, 1002 |
| 1, 1004 |
| 1, 1003 |
+ ------ +
Rows in set (0.00 sec)

Merging is easy to understand, that is, merging the results of multiple queries and removing duplicate records.

To save duplicate records, use the union all statement.