Introduction to msyql subquery IN, EXISTS, ANY, ALL, UNION

IN mysql, A subquery is the query result of an inner query statement nested IN another query statement. It can provide query conditions for outer query statements. mysql commonly uses subqueries IN, EXISTS, ANY, ALL, UNION. I will introduce them one by one.

IN mysql, A subquery is the query result of an inner query statement nested IN another query statement. It can provide query conditions for outer query statements. mysql commonly uses subqueries IN, EXISTS, ANY, ALL, UNION. I will introduce them one by one.

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

Subquery with IN keyword

The IN operator is used IN the WHERE expression to support multiple choices IN the form of list items. The syntax is as follows:

WHERE column IN (value1, value2 ,...)
WHERE column not in (value1, value2 ,...)
When the NOT operator is added before the IN operator, it indicates the opposite meaning of the IN operator, that is, it is NOT selected IN these list items.

The Code is as follows:
Query Select id, name from a where id in (select aid from B) // query the AID records IN Table B Select id, name from a where id not in (select aid from B) indicates the opposite of the preceding Delete Delete from articles where id in (1, 2, 3); // delete records with id = 1, id = 2, id = 3 Delete from articles where id not in (1); // delete id! Record = 1

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

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

2 rows in set (0.00 sec) It looks a bit complicated to find out which departments are not 24-year-olds

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

Subquery with EXISTS keyword

The subquery Syntax of MySQL EXISTS and not exists is as follows:

SELECT... FROM table where exists (subquery)
This syntax can be understood as: Put the data of the primary query into the subquery for conditional verification, and determine whether the data results of the primary query are retained based on the verification result (TRUE or FALSE.

The 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

The Code is as follows:
Mysql> SELECT * FROM employee -> WHERE age> 24 AND EXISTS -> (SELECT d_name FROM department WHERE d_id = 1003 );

Prompt
• EXISTS (subquery) Only returns TRUE or FALSE. Therefore, SELECT * in the subquery can be SELECT 1 or another. The official statement is that the SELECT list is ignored during actual execution, so there is no difference.
• The actual execution process of the EXISTS subquery may have been optimized rather than compared one by one on our understanding. If you are concerned about efficiency, you can perform a practical test to determine whether the problem is efficient.
• EXISTS subqueries can also be replaced by conditional expressions, other subqueries, or joins. What kind of Optimization requires detailed analysis of specific problems?

Subquery with ANY keyword

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

The Code is as follows:
Mysql> SELECT * FROM employee -> WHERE d_id! = ANY -> (SELECT d_id FROM department ); 4 rows in set (0.00 sec)

Subquery with the ALL keyword
The ALL keyword indicates that ALL conditions are satisfied.

The Code is as follows:
Mysql> SELECT * FROM employee -> WHERE d_id> = ALL -> (SELECT d_id FROM department );

1 row in set (0.00 sec) does not understand what these two statements mean for the moment.

UNION merge query results

MySQL UNION is used to combine the results from multiple SELECT statements into a single result set. Syntax:

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
...
In multiple SELECT statements, the corresponding column should have the same field attribute, and the field name used in the first SELECT statement is also used for the result field name.

Difference between UNION and UNION ALL
When UNION is used, MySQL deletes the repeated records in the result set. When union all is used, MySQL returns ALL records, and the efficiency is higher than UNION.

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

4 rows in set (0.00 sec) Merge is better understood, that is, merge the results of multiple queries, and then remove duplicate records.

To save duplicate records, use the union all statement.