Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

any operator
sql
communitycreator

What is ANY Operator in SQL?

Salman Yousaf

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.

RELATED TAGS

any operator
sql
communitycreator
RELATED COURSES

View all Courses

Keep Exploring