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:

</>
Copy
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:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

</>
Copy
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:

</>
Copy
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:

</>
Copy
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);