SQL Basics

SQL Functions

SQL Joins

SQL Advanced

SQL Database

SQL Tables

SQL Constraints

SQL Performance

SQL Data Types

SQL Security

SQL ANY and ALL

❮ Previous Next ❯

The SQL ANY and ALL Operators

The ANY and ALL operators are used with a WHERE or HAVING clause. They allow you to perform a comparison between a single column value and a range of other values.

The ANY Operator

The ANY operator:

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 (=, <, >, <=, >=, <>).

The ALL Operator

The ALL operator:

ALL Syntax

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

Demo Database

Below is a selection from the Customers table:

| Customers Table: |

CustomerID CustomerName Country
1 Alfreds Futterkiste Germany
2 Ana Trujillo Emparedados Mexico
3 Antonio Moreno Taquería Mexico
4 Around the Horn UK
5 Berglunds snabbköp Sweden

ANY Example

The following SQL statement lists the CustomerName if it finds that the country is ANY of the countries returned by the subquery.

ANY Example

SELECT CustomerName
FROM Customers
WHERE Country = ANY (SELECT Country FROM Customers WHERE Country = 'Mexico');

This would return "Ana Trujillo Emparedados" and "Antonio Moreno Taquería", as their country matches the subquery result.

ALL Example

The following SQL statement lists CustomerName if the country is alphabetically greater than ALL countries returned by the subquery. This is a much stricter condition.

ALL Example

SELECT CustomerName
FROM Customers
WHERE Country > ALL (SELECT Country FROM Customers WHERE Country = 'Germany' OR Country = 'Mexico');

This query would return customers from "Sweden" and "UK", as these country names are alphabetically after both "Germany" and "Mexico".

❮ Previous Next ❯