SQL Set operator use method _mssql

(1) In operator: it can be used to match an item in a fixed set. For example, the year in a collection has (2001,2003,2005), then you can have:

Copy Code code as follows:

SELECT * from T_book
WHERE fyearpublished in (2001,2003,2005)

The In operator, in addition to supporting the matching from the fixed set, of course, also supports the dynamic collection way to match. For example, the following ways:

Copy Code code as follows:

SELECT * from T_reader
WHERE Fyearofjoin in
(
Select fyearpublished from T_book
)

(2) Any and some set operators: In SQL Server, any and some are synonyms, both of which have the same usage and function (two of them, not knowing if the egg hurts). Any and some need to be used in conjunction with other comparison characters (greater than (>), equal to (=), less than (<), greater than or equal to (>=), less than, and so on) compared to the in operator, and the comparator needs to precede them.

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

SELECT * from T_book
WHERE Fyearpublished<any (2001,2003,2005)

(3) All set operator: In SQL Server, the all operator also needs to be used in conjunction with other comparison characters (greater than (>), equal to (=), less than (<), greater than or equal to (>=), less than, and so on), and the comparator needs to precede them.

Copy Code code as follows:

SELECT * from T_book
WHERE Fyearpublished<all
(
SELECT Fyearofjoin from T_reader
)

Attention:

I, as with any and some operators, the all operator can also not match a fixed set, for example, the following SQL statement is incorrect:

Copy Code code as follows:

SELECT * from T_book
WHERE Fyearpublished<all (2001,2003,2005)

Ii. with regard to the use of the all operator, there is also the need to note that when this all subquery result is empty, the result of the match is not handled in an empty manner, but is equivalent to the success of all matches. So when using the all operator, this problem can easily cause bugs in the system, so it must be noted when used. Like what:

Copy Code code as follows:

SELECT * from T_book
WHERE Fyearpublished<all
(
SELECT Fyearofjoin from T_reader
WHERE fprovince = ' Jiangsu '
)

If the result of the all subquery is empty, the entire result of the Select Fyearofjoin from T_reader is taken as the result of a successful match.

(4) The EXISTS set operator: Unlike the In, any, SOME, and all operators, the EXISTS operator is the monocular operator and does not match the column, so it does not require that the set to be matched be single-column. The EXISTS operator is used to check whether each row matches a subquery, and it can be assumed that exists is used to test whether the result of a subquery is null, and if the result set is NULL, the match is false, otherwise the result is true.

Copy Code code 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 exists subquery, SQL matches each row of data in the T_category table to the subquery, testing the T_book table for the existence of a Fcategoryid field value equal to the current class primary key value and the publication year before 1950.