SQL set operator usage

(1) in operator: it can be used to match an item IN a fixed set. For example, if the year in a set is (2005,), you can:

Copy codeThe Code is as follows:
SELECT * FROM T_Book
WHERE FYearPublished IN (2005)

The IN operator not only supports matching from a fixed set, but also supports dynamic set matching. For example:

Copy codeThe Code is as follows:
SELECT * FROM T_Reader
WHERE FYearOfJoin IN
(
Select FYearPublished FROM T_Book
)

(2) ANY and SOME collection operators: in SQL server, ANY and SOME are synonyms. The usage and functions of the two operators are the same (we also have two features, so I don't know if it is a pain point ). Compared with the IN operator, ANY and SOME must be compared with other operators (greater than (>), equal to (=), less than (<), greater than or equal to (> =), less than, and so on) and the comparison operators must be in front of them.
Copy codeThe Code is as follows:
SELECT * FROM T_Reader
WHERE FYearOfJoin = ANY
(
Select FYearPublished FROM T_Book
)

Note: Unlike the IN operator, the ANY and SOME operators cannot match a fixed set. For example, the following SQL statement is incorrect:

Copy codeThe Code is as follows:
SELECT * FROM T_Book
WHERE FYearPublished <ANY (2005)

(3) ALL set operators: in SQL SERVER, ALL operators also need to be compared with other operators (greater than (>), equal to (=), less than (<), greater than or equal to (> =), less than, and so on), and the comparison operators must be before them.
Copy codeThe Code is as follows:
SELECT * FROM T_Book
WHERE FYearPublished <ALL
(
SELECT FYearOfJoin FROM T_Reader
)

Note:

I. It is the same as ANY and SOME operators, and the ALL operator cannot match a fixed set. For example, the following SQL statement is incorrect:
Copy codeThe Code is as follows:
SELECT * FROM T_Book
WHERE FYearPublished <ALL (2005)

II. Note that when the ALL operator is used, when the ALL subquery result is null, the matching result is not processed in an empty way, but is equivalent to a successful match. Therefore, when using the ALL operator, this issue can easily cause bugs in the system. Therefore, you must pay attention to this issue during use. For example:
Copy codeThe Code is as follows:
SELECT * FROM T_Book
WHERE FYearPublished <ALL
(
SELECT FYearOfJoin FROM T_Reader
WHERE FProvince = 'jiangsu'
)

If the results of ALL subqueries are null, ALL results of SELECT FYearOfJoin FROM T_Reader will be taken as the matching result.

(4) EXISTS set operator: unlike IN, ANY, SOME, ALL, and other operators, EXISTS operator is a single object operator that does not match a column, therefore, it does not require the set to be matched to be a single column. The EXISTS operator is used to check whether each row matches a subquery. It can be considered that EXISTS is used to test whether the subquery result is null. If the result set is null, the matching result is false, otherwise, the matching result is true.

Copy codeThe Code is as follows:
SELECT * FROM T_Category
WHERE EXISTS
(
SELECT * FROM T_Book
WHERE T_Book. FCategoryId = T_Category.FId
AND T_Book. FYearPublished <1950
)

In the subquery after EXISTS, SQL matches each row of data in the T_Category table to the subquery, test whether the value of the FCategoryId field in the T_Book table is equal to the value of the primary key of the current category and is published before January 1, 1950.