SQL ALL Operator – Syntax and Examples

SQL ALL Operator

The SQL ALL operator is used to compare a value to all values in a specified list or subquery. When used with comparison operators like =, >, <, >=, and <=, the ALL operator returns TRUE only if the comparison holds true for every value in the list or subquery.

In this tutorial, we will go through SQL ALL Operator, its syntax, and how to use this operator in to compare a value to all values in a specified list or subquery in SQL statements, with well detailed examples.

---

Syntax of SQL ALL Operator

The basic syntax of the SQL ALL operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator ALL (subquery);

Each part of this syntax has a specific purpose:

• SELECT: Specifies the columns to retrieve from the table.
• FROM: Specifies the table from which to retrieve data.
• WHERE: Introduces the condition used to filter the data.
• operator: Any comparison operator like =, >, or <.
• ALL: Returns TRUE if the comparison is true for every value in the subquery.
• subquery: A query that returns a set of values for comparison.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the ALL operator in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality along with a grades table that records student grades.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

INSERT INTO grades (student_id, subject, score)
VALUES
(1, 'Math', 85),
(2, 'Math', 78),
(3, 'Science', 90),
(4, 'Math', 88),
(5, 'Science', 70);

---

Examples: Using ALL Operator in Queries

Now, let’s explore different scenarios of using the ALL operator with these tables.

---

1. Using ALL with Greater Than (>) Operator

To select students who are older than all students in the 8th grade:

SELECT * FROM students
WHERE age > ALL (SELECT age FROM students WHERE grade = '8th');

This query returns students who are older than every student in the 8th grade, displaying records for students who meet this criterion.

---

2. Using ALL with Less Than (<) Operator

To select students who have a score less than all scores in the “Math” subject:

SELECT * FROM students
WHERE id IN (
	SELECT student_id FROM grades
    WHERE score < ALL (
		SELECT score FROM grades 
			WHERE subject = 'Math'
		)
	);

This query returns students with scores lower than every score in the “Math” subject, displaying the records of those matching students.

---

3. Using ALL with Equals (=) Operator

To select students whose scores match all scores in the “Science” subject:

SELECT * FROM students
WHERE id = ALL (
	SELECT student_id FROM grades 
    WHERE score = 90
);

This query finds students with a score equal to all the scores in “Science”, displaying the records of those students.

---

4. Using ALL to Verify Multi-Condition Checks Across Tables

To select students in “Northside” who have scored higher than all scores received by students in “Westend”:

SELECT * FROM students
WHERE locality = 'Northside' AND id IN (
	SELECT student_id FROM grades
    WHERE score > ALL (
		SELECT score FROM grades JOIN students ON grades.student_id = students.id
        WHERE locality = 'Westend'
	)
);

This query checks if students in “Northside” have scores higher than every score achieved by students in “Westend”, displaying matching students’ records.

---

FAQs for SQL ALL

1. What does the SQL ALL operator do?

The ALL operator compares a value to all values in a subquery. The condition returns TRUE only if the comparison holds true for every value in the subquery.

2. Can I use ALL with different comparison operators?

Yes, you can use ALL with =, >, <, >=, and <= to compare values against every value in the subquery result.

3. How is ALL different from ANY in SQL?

ALL returns TRUE only if a condition is true for every value in a subquery, whereas ANY returns TRUE if the condition is true for at least one value.

Reference: SQL ANY Operator

4. Can I use ALL with a static list of values instead of a subquery?

No, ALL requires a subquery and cannot be used with a specific list of values. Use IN if you want to compare to a fixed list.

5. Can ALL be combined with AND and OR operators?

Yes, you can combine ALL with AND, OR, and other operators to create complex conditions for filtering results.

Reference:
SQL AND Operator
SQL OR Operator