Which SQL operator is used to check whether an attribute value matches any value within a value list?

Used to check whether an attribute value is within a rangeIS NULL:Used to check whether an attribute value is nullLIKE:Used to check whether an attribute value matches a given string patternIN:Used to check whether an attribute value matches any value within a value listEXISTS:Used to check whether a subquery returns any rowsSQL commands can be divided into two overall categories: data definition language (DDL)commands and data manipulation language (DML) commands.The basic data definition commands allow you to create tables and indexes. Many SQLconstraints can be used with columns. The commands are CREATE TABLE, CREATE INDEX,ALTER TABLE, DROP TABLE, and DROP INDEX.DML commands allow you to add, modify, and delete rows from tables. The basic DMLcommands are SELECT, INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK.The INSERT command is used to add new rows to tables. The UPDATE command is used tomodify data values in existing rows of a table. The DELETE command is used to delete rowsfrom tables. The COMMIT and ROLLBACK commands are used to permanently save or roll

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

Table of Contents Show

The SQL ANY Operator
The SQL ALL Operator
ALL Syntax With SELECT
ALL Syntax With WHERE or HAVING
Demo Database
SQL ANY Examples
SQL ALL Examples
Introduction to the SQLite IN operator
SQLite IN operator examples
SQLite IN operator with a subquery example
SQLite NOT IN examples

The SQL ANY Operator

The ANY operator:

returns a boolean value as a result
returns TRUE if ANY of the subquery values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the range.

ANY Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

The SQL ALL Operator

The ALL operator:

returns a boolean value as a result
returns TRUE if ALL of the subquery values meet the condition
is used with SELECT, WHERE and HAVING statements

ALL means that the condition will be true only if the operation is true for all values in the range.

ALL Syntax With SELECT

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

ALL Syntax With WHERE or HAVING

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Demo Database

Below is a selection from the "Products" table in the Northwind sample database:

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 - 12 oz bottles	19
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10
4	Chef Anton's Cajun Seasoning	2	2	48 - 6 oz jars	22
5	Chef Anton's Gumbo Mix	2	2	36 boxes	21.35
6	Grandma's Boysenberry Spread	3	2	12 - 8 oz jars	25
7	Uncle Bob's Organic Dried Pears	3	7	12 - 1 lb pkgs.	30
8	Northwoods Cranberry Sauce	3	2	12 - 12 oz jars	40
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97

And a selection from the "OrderDetails" table:

OrderDetailID	OrderID	ProductID	Quantity
1	10248	11	12
2	10248	42	10
3	10248	72	5
4	10249	14	9
5	10249	51	40
6	10250	41	10
7	10250	51	35
8	10250	65	15
9	10251	22	6
10	10251	57	15

SQL ANY Examples

The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails table has Quantity equal to 10 (this will return TRUE because the Quantity column has some values of 10):

SELECT ProductNameFROM ProductsWHERE ProductID = ANY (SELECT ProductID FROM OrderDetails

WHERE Quantity = 10);

Try it Yourself »

The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails table has Quantity larger than 99 (this will return TRUE because the Quantity column has some values larger than 99):

SELECT ProductNameFROM ProductsWHERE ProductID = ANY (SELECT ProductID FROM OrderDetails

WHERE Quantity > 99);

Try it Yourself »

The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails table has Quantity larger than 1000 (this will return FALSE because the Quantity column has no values larger than 1000):

SELECT ProductNameFROM ProductsWHERE ProductID = ANY (SELECT ProductID FROM OrderDetails

WHERE Quantity > 1000);

Try it Yourself »

SQL ALL Examples

The following SQL statement lists ALL the product names:

SELECT ALL ProductNameFROM Products

WHERE TRUE;

Try it Yourself »

The following SQL statement lists the ProductName if ALL the records in the OrderDetails table has Quantity equal to 10. This will of course return FALSE because the Quantity column has many different values (not only the value of 10):

SELECT ProductNameFROM ProductsWHERE ProductID = ALL (SELECT ProductID FROM OrderDetails

WHERE Quantity = 10);

Try it Yourself »

Summary: in this tutorial, you will learn how to use the SQLite IN operator to determine whether a value matches any value in a list of values or a result of a subquery.

Introduction to the SQLite IN operator

The SQLite IN operator determines whether a value matches any value in a list or a subquery. The syntax of the IN operator is as follows:

expression [NOT] IN (value_list|subquery);

Code language: SQL (Structured Query Language) (sql)

The expression can be any valid expression or a column of a table.

A list of values is a fixed value list or a result set of a single column returned by a subquery. The returned type of expression and values in the list must be the same.

The IN operator returns true or false depending on whether the expression matches any value in a list of values or not. To negate the list of values, you use the NOT IN operator.

SQLite IN operator examples

We will use the Tracks table from the sample database for the demonstration.

The following statement uses the IN operator to query the tracks whose media type id is 1 or 2.

SELECT TrackId, Name, Mediatypeid FROM Tracks WHERE MediaTypeId IN (1, 2) ORDER BY Name ASC;

Code language: SQL (Structured Query Language) (sql)

Try It

This query uses the OR operator instead of the IN operator to return the same result set as the above query:

SELECT TrackId, Name, MediaTypeId FROM Tracks WHERE MediaTypeId = 1 OR MediaTypeId = 2 ORDER BY Name ASC;

Code language: SQL (Structured Query Language) (sql)

Try It

As you can see from the queries, using the IN operator is much shorter.

If you have a query that uses many OR operators, you can consider using the IN operator instead to make the query more readable.

SQLite IN operator with a subquery example

The following query returns a list of album id of the artist id 12:

SELECT albumid FROM albums WHERE artistid = 12;

Code language: SQL (Structured Query Language) (sql)

Try It

To get the tracks that belong to the artist id 12, you can combine the IN operator with a subquery as follows:

SELECT TrackId, Name, AlbumId FROM Tracks WHERE AlbumId IN ( SELECT AlbumId FROM Albums WHERE ArtistId = 12 );

Code language: SQL (Structured Query Language) (sql)

Try It

In this example:

First, the subquery returns a list of album ids that belong to the artist id 12.
Then, the outer query return all tracks whose album id matches with the album id list returned by the subquery.