MSYQL Neutron Query In,exists,any,all,some,union Introduction

1.ANY Keywords

Assuming that the number of results returned by a query statement inside any is three, such as: RESULT1,RESULT2,RESULT3,

Select ... from ... where a > any (...);

-

Select ... where a > result1 or a > result2 or a > result3;

2.ALL Keywords

The all keyword is similar to the ANY keyword except that the or is changed to and. That

Select ... where a > All (...);

-

Select ... where a > Result1 and a > Result2 and a > result3;

3.SOME Keywords

The Some keyword and the Any keyword are the same functionality. So:

Select ... where a > some (...);

-

Select ... where a > result1 or a > result2 or a > result3;

4.IN Keywords

The in operator is used in the where expression to support multiple selections in the form of list items, with the following syntax:

WHERE column in (Value1,value2,...)
WHERE column not in (Value1,value2,...)
When in is preceded by the not operator , it means the opposite of in, that is, not selected within these list items. The code is as follows:

EnquirySELECTId,name fromAWHEREIdinch(SELECTAID fromB//querying the records of aid in table BSELECTId,name fromAWHEREId not inch(SELECTAID fromB)//meaning and reverse above deleteDelete  fromArticleswhereIdinch(1,2,3);//Delete ID=1Id=2Id=3 of RecordsDelete  fromArticleswhereId not inch(1);//Delete ID!=1 of records

The word in is an alias of "=any". Therefore, the two statements are the same:

SELECT  from WHERE =  any (SELECT from T2); SELECT  from WHERE inch    (SELECT from T2);

5.EXISTS Keywords

The MySQL EXISTS and not EXISTS subquery syntax are as follows:

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

MySQL>SELECT* from employee    ,WHERE  EXISTS     ,SELECTfromWHERE d_id=1004 Set (0.00 sec)  

The inner loop here does not query the result that satisfies the condition, so returns false and the outer query does not execute.

Not EXISTS just the opposite .

Of course, the EXISTS keyword can be used with other query conditions, and the conditional expression is connected to the exists keyword with an and OR OR, as follows:

MySQL>SELECT* from employee    --WHERE Age >  -  and EXISTS     - (SELECTfromWHERE d_id=1003);

Tips:
exists (subquery) returns only TRUE or FALSE, so select * in a subquery can also be select 1 or other, 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 one by one, and if you are concerned about efficiency, you can conduct a practical test to determine whether there is an efficiency problem.
exists subqueries can also be substituted by conditional expressions, other subqueries, or joins, and what is optimal requires specific analysis

6.UNION Keywords

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 property , and the field name used in the first SELECT statement is also used for the field names of the results .

Union and UNION ALL differences

When you use Union , MySQL deletes the duplicate records in the result set, and with union all, MySQL returns all records and is more efficient than union.

Mysql> SELECTd_id fromEmployee - UNION     - SELECTd_id fromDepartment;+------+|d_id|+------+| 1001 || 1002 || 1004 || 1003 |+------+

Merging is a good understanding, which is to combine the results of multiple queries and then remove duplicate records , and if you want to save duplicate records, you can use the UNION all statement.

MSYQL Neutron Query In,exists,any,all,some,union Introduction