SQL ALL Keyword

The ALL keyword is used with a comparison operator to return TRUE if the comparison is true for all values returned by a subquery.

SQL ALL Syntax

The operator can be: =, !=, >, <, >=, <=

SELECT column_name(s)
FROM table_name
WHERE expression operator ALL (subquery);

SELECT column_name(s)

FROM table_name

WHERE expression operator ALL (subquery);

SQL ALL Example

This query attempts to select patients whose patient_id matches all the patient IDs from the Appointments table with a specific doctor_id (e.g. 1):

SELECT first_name, last_name
FROM Patients
WHERE patient_id = ALL (
  SELECT patient_id
  FROM Appointments
  WHERE doctor_id = 1
);

SELECT first_name, last_name

FROM Patients

WHERE patient_id = ALL (

  SELECT patient_id

  FROM Appointments

  WHERE doctor_id = 1

);

Important: The ALL keyword with subqueries causes a syntax error in our playground environment.
You will see: near "ALL": syntax error.
This feature is not supported, and you should use alternative approaches such as IN, JOIN, or NOT EXISTS where applicable.

SQL ALL Labs