A subquery is nested within another query statement by nesting a query statement
Query results for inner query statements that provide query conditions for outer query statements
Because in a particular case, the condition of one query statement requires another query statement to obtain
Reference table: Employee
Reference table: Department
subquery with in keyword
Copy Code code as follows:
Mysql> SELECT * FROM employee
-> WHERE d_id in
-> (SELECT d_id from Department);
+------+------+--------+------+------+--------------------+
| num | d_id | name | Age | sex | homeaddr |
+------+------+--------+------+------+--------------------+
| 1 | 1001 | John | 26 | Male | Haidian District, Peking City |
| 2 | 1001 | Dick | 24 | Female | Beijing Changping District |
| 3 | 1002 | Harry | 25 | Male | Changsha City, Hunan |
+------+------+--------+------+------+--------------------+
Rows in Set (0.00 sec)
Here you first query the information for all the D_ID fields in the Department table and use the results as criteria
Then query the Employee table for all field information in d_id condition
The effect of not in is just the opposite of the above
Subqueries with comparison operators
Copy Code code 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)
Find out which departments do not have an age of 24 employees, it looks a little complicated
In addition, there are a lot of operators, there is no longer repeat
Subquery with exists keyword
The EXISTS keyword indicates existence. When using the EXISTS keyword, the inner query statement does not return a record of the query, but returns a true or False value, returning true if the inner query statement queries the record that satisfies the condition, or returns false
When the value returned is true, the outer query statement is queried, otherwise no query is made
Copy Code code 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 here does not query for the result of satisfying the condition, so returns false, the outer query does not execute
Not exists just the opposite
Of course, the EXISTS keyword can be used in conjunction with other query criteria
Conditional expressions are connected with the EXISTS keyword with and OR or
Copy Code code 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 | John | 26 | Male | Haidian District, Peking City |
| 3 | 1002 | Harry | 25 | Male | Changsha City, Hunan |
+------+------+--------+------+------+--------------------+
Rows in Set (0.00 sec)
subquery with any keyword
Any keyword indicates that any of these conditions are met
Copy Code code as follows:
Mysql> SELECT * FROM employee
-> WHERE D_id!=any
-> (SELECT d_id from Department);
+------+------+--------+------+------+--------------------+
| num | d_id | name | Age | sex | homeaddr |
+------+------+--------+------+------+--------------------+
| 1 | 1001 | John | 26 | Male | Haidian District, Peking City |
| 2 | 1001 | Dick | 24 | Female | Beijing Changping District |
| 3 | 1002 | Harry | 25 | Male | Changsha City, Hunan |
| 4 | 1004 | Aric | 15 | Male | England |
+------+------+--------+------+------+--------------------+
Rows in Set (0.00 sec)
Subqueries with the ALL keyword
The all keyword indicates that all of the conditions are met
Copy Code code 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 | England |
+------+------+------+------+------+----------+
Row in Set (0.00 sec)
Temporarily do not understand what these two statements mean, then fill up on
Union Merge Query Results
Copy Code code as follows:
Mysql> SELECT d_id from employee
-> UNION
-> SELECT d_id from department;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1004 |
| 1003 |
+------+
Rows in Set (0.00 sec)
Merging is better understood by merging the results of multiple queries and then removing duplicate records from them
If you want to save duplicate records, you can use the UNION ALL statement
