What is ANY Operator in SQL?

Overview

SQLStructured Query Language is a domain-specific programming language used to manage relational databases. It helps to performCRUD operations CRUDcreate, read, update and delete operations on records in the databases.

ANY operator in SQL allows us to compare a single column value and a range of other values.

Syntax

SELECT <column_names>

FROM <table_name>

WHERE <column_name> <operator> ANY

  (SELECT <column_name>

  FROM <table_name>

  WHERE <condition>);

Return value

It returns a boolean value. When any subquery value meets the specified condition, it returns True. otherwise, it returns False.

Code

We'll create a company database that has two tables: worker and bonus. Each table has multiple properties and uses worker_id as a unique identifier or primary key.

CREATE DATABASE company;

use company;

-- Creating a worker table

CREATE table worker(

worker_id int,

first_name varchar(100), 

last_name varchar(100),

salary int,

department varchar(15),

PRIMARY KEY(worker_id));

-- Creating a bonus table

CREATE table bonus(

worker_id int,

bonus_date datetime,

bonus_amt int,

FOREIGN KEY(worker_id) REFERENCES worker(worker_id));

-- inserting record in worker table

INSERT INTO worker values (1, "Charlie", "Arora", 100000, "Engineering");

INSERT INTO worker values (2, "Niharika", "Verma", 80000, "Admin");

INSERT INTO worker values (3, "Thomas", "Martin", 300000, "HR");

INSERT INTO worker values (4, "William", "O'Brien", 500000, "Admin");

INSERT INTO worker values (5, "Vivek", "Bhati", 500000, "Admin");

INSERT INTO worker values (6, "Vipul", "Diwan", 200000, "Account");

INSERT INTO worker values (7, "Satish", "Kumar", 75000, "Account");

INSERT INTO worker values (8, "Anderson", "Lee", 90000, "Admin");

-- inserting record in bonus table

INSERT INTO bonus values (1,'2021-03-20 00:00:00',5000);

INSERT INTO bonus values (2,'2021-06-12 00:00:00',5000);

INSERT INTO bonus values (3,'2021-02-15 00:00:00',4000);

INSERT INTO bonus values (1,'2021-02-19 00:00:00',4500);

INSERT INTO bonus values (2,'2021-06-15 00:00:00',3500);

-- Query to extact name of employee who has bonus amt 5000

-- with using ANY operator

SELECT concat(first_name, ' ', last_name) as Name FROM worker

WHERE worker_id = ANY

  (SELECT worker_id

  FROM bonus

  WHERE bonus_amt = 5000);

Explanation

  • Lines 1 and 2: We create and use a database named company. CREATE DATABASE statement in SQL is used to create a new database while USE helps to load.
  • Lines 4 to 10: We create a worker table including worker_id, first_name, last_name, salary, and department as fields.
  • Lines 12 to 16: We create a bonus table with worker_id, bonus_date and bonus_amt as table fields.
  • Lines 18 to 25: We insert eight observations in the worker's table.
  • Lines 27 to 31: We insert five entries in the bonus table.
  • Lines 34 to 38: SQL query extracts the employees' full name who has a bonus amount of 5000 using ANY operator.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)