Introduction of MSYQL Neutron query in,exists,any,all,union

Because in a particular case, the condition of one query statement requires another query statement to obtain

subquery with in keyword

The in operator is used in a WHERE expression to support multiple selections in the form of list items, as follows:

WHERE column in (Value1,value2,...)
WHERE column not in (Value1,value2,...)
When in front plus the not operator, the expression is in the opposite meaning, that is, not selected within these list items.

The code is as follows	Copy Code
Inquire Select Id,name a WHERE ID in (select aid from B)//Check the records of AID in table B Select Id,name from, WHERE ID not in (select AID from B) means the opposite Delete Delete from articles where ID in (1,2,3); Delete a id=1,id=2,id=3 record Delete from articles where ID isn't in (1);//Deletes id!=1 record

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

The code is as follows	Copy Code
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) to 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 MySQL EXISTS and not EXISTS subquery syntax is as follows:

SELECT ... From table WHERE EXISTS (subquery)
The syntax can be understood to be that the data of the main query is placed in a subquery for conditional validation, depending on the validation result (TRUE or FALSE) to determine whether the data results of the main query are preserved.

The code is as follows	Copy Code
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

The code is as follows	Copy Code
Mysql> SELECT * FROM employee -> WHERE age>24 and EXISTS -> (SELECT d_name from Department WHERE d_id=1003);

Tips
exists (subquery) returns TRUE or FALSE only, so the SELECT * in a subquery can also be select 1 or otherwise, and the official claim is that the select list is ignored when actually executed, so there is no difference.
The actual execution of the exists subquery may be optimized rather than a one-article comparison of our understanding, and if the problem of efficiency is concerned, the actual test can be conducted to determine whether the problem is efficient.
exists subqueries can often be replaced with conditional expressions, other subqueries, or joins, and what kind of optimal needs specific analysis

subquery with any keyword

Any keyword indicates that any of these conditions are met

The code is as follows	Copy Code
Mysql> SELECT * FROM employee -> WHERE D_id!=any -> (SELECT d_id from Department); 4 rows in Set (0.00 sec)

Subqueries with the ALL keyword
The all keyword indicates that all of the conditions are met

The code is as follows	Copy Code
Mysql> SELECT * FROM employee -> WHERE D_id>=all -> (SELECT d_id from Department);

1 row in Set (0.00 sec) Don't understand what these two statements mean.

Union Merge Query Results

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

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

The difference between union and all
When using union, MySQL deletes duplicate records from the result set, and with union ALL, MySQL returns all records and is more efficient than union.

The code is as follows	Copy Code
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 better understood, that is, 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