SQL ANY Operator – Syntax and Examples

SQL ANY Operator

The SQL ANY operator is used to compare a value to any value in a specified list or subquery. When used with comparison operators like =, >, <, >=, and <=, the ANY operator returns TRUE if the comparison is true for at least one value in the list or subquery.

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

---

Syntax of SQL ANY Operator

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

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator ANY (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 <.
• ANY: Returns TRUE if the comparison is true for any 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 ANY 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 ANY Operator in Queries

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

---

1. Using ANY with Greater Than (>) Operator

To select students who have a higher score than any score received by students in the 9th grade:

SELECT * FROM students
WHERE age > ANY (SELECT age FROM students WHERE grade = '9th');

This query returns students whose age is greater than any single age value in the 9th grade, displaying records for students who meet this criterion.

---

2. Using ANY with Less Than (<) Operator

To select students who have a score less than any score in “Science” subject:

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

This query returns students who have scores lower than any of the scores in “Science”, displaying records for those matching students.

---

3. Using ANY with Equals (=) Operator

To select students whose score in any subject equals 90:

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

This query finds students with any score that equals 90, displaying the records of those students.

---

4. Using ANY with Subquery for Multi-Condition Check

To select students in “Northside” who scored higher than any score received by students in “Westend”:

SELECT * FROM students
WHERE locality = 'Northside' AND id IN (SELECT student_id FROM grades WHERE score > ANY (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 any score by students in “Westend”, displaying matching students rows.

---

FAQs for SQL ANY

1. What does the SQL ANY operator do?

The ANY operator allows you to compare a value to any value returned by a subquery. If the comparison holds true for any of those values, the condition returns TRUE.

2. Can I use ANY with different comparison operators?

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

3. How is ANY different from ALL in SQL?

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

Reference: SQL ALL Operator

4. Can I use ANY with a specific list of values instead of a subquery?

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

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

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

Reference:
SQL AND Operator
SQL OR Operator