MySQL study notes 16: subquery

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

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, Hunan Province |  +  --  ---- + ------ + -------- + ------ + -------------------- +  3 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

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 |  +  -- ---- + ----------- +  2 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.

MySQL> Select * FromEmployee-> Where Exists->(SelectD_nameFromDepartmentWhereD_id=1004); EmptySet(0.00Sec)
 

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

MySQL >   Select   *   From  Employee  ->   Where Age >  24   And   Exists      -> ( Select D_name From DepartmentWhere 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, Hunan Province |  +  --  ---- + ------ + -------- + ------ + -------------------- +  2 Rows In   Set (0.00 Sec)

 

Subquery with any keyword

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

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, Hunan Province |  |     4   |   1004   | Aric |     15   | Male | England |  +  --  ---- + ------ + -------- + ------ + -------------------- +  4 Rows In  Set ( 0.00 Sec)

 

Subquery with the All keyword

The All keyword indicates that all conditions are satisfied.

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 |  +  --  ---- + ------ + ---------- +  1 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

MySQL >   Select D_id From  Employee  ->   Union      ->   Select D_id From  Department;  + --  ---- +  | D_id |  +  --  ---- +  |   1001   |  |   1002   |  |   1004   |  |  1003   |  +  --  ---- +  4 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.