How to use the set operator in sql

It is believed that many inexperienced people have no idea about how to use the set operator in sql. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

(1) IN operator: it can be used to match an item in a fixed set. For example, if the years in a collection are (2001 ~ 2003 ~ 2005), then you can have:

The copy code is as follows: SELECT * FROM T_BookWHERE FYearPublished IN (2001, 2003, 2005)

The IN operator, in addition to supporting matching from a fixed collection, also supports dynamic set matching. For example, as follows:

The copy code is as follows: SELECT * FROM T_ReaderWHERE FYearOfJoin IN (select FYearPublished FROM T_Book)

(2) ANY and SOME set operators: in SQL SERVER, ANY and SOME are synonyms, and they have the same usage and function. Compared to the IN operator, ANY and SOME need to be used with other comparators (greater than (>), equal to (=), less than (=), less than, and so on), and the comparator needs to precede them. The copy code is as follows: SELECT * FROM T_ReaderWHERE FYearOfJoin = ANY (select FYearPublished FROM T_Book)

Note: unlike the IN operator, the ANY and SOME operators cannot match a fixed collection, for example, the following SQL statement is incorrect:

The copy code is as follows: SELECT * FROM T_BookWHERE FYearPublished), equal to (=), less than (=), less than, etc.), and the comparator needs to precede them. The copy code is as follows: SELECT * FROM T_BookWHERE FYearPublished