What is the ALL operator in SQL?

Overview

SQL (Structured Query Language) is a domain-specific programming language that manages relational databases.

The ALL operator returns True when all subquery values meet the specified condition. It is used with the SELECT, WHERE, and HAVING SQL statements.

Syntax

SELECT ALL (column_names)

FROM table_name

WHERE condition;

Return value

This operator returns a boolean value that is either True or False.

Explanation

In this code snippet, we create a company database. It has two tables: worker and bonus. Each table has multiple attributes, and we use worker_id as a 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(100),

PRIMARY KEY(worker_id));

-- Creating a bonus table

CREATE table bonus(

worker_id int,

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, 5000);

INSERT INTO bonus values (2, 5500);

INSERT INTO bonus values (3, 4000);

INSERT INTO bonus values (1, 4500);

INSERT INTO bonus values (2, 3500);

-- Query to extract first name of employee(s) who has bonus amt 5000

-- with using All operator

SELECT first_name

FROM worker

WHERE worker_id = ALL 

  (SELECT worker_id

  FROM bonus

  WHERE bonus_amt = 5000);
  • Line 1-2: We create and use a company database. The CREATE DATABASE statement creates a database while USE loads the database.
  • Line 4-10: We create the worker table with worker_id, first_name, last_name, salary, and department as table attributes and make worker_id as the primary key.
  • Line 12-15: We create the bonus table with worker_id and bonus_amt as attributes. We make worker_id as the foreign key associated with the worker_id from the worker table.
  • Line 17-24: We use the INSERT INTO statement to insert the data in the worker table.
  • Line 26-30: We use the INSERT INTO statement to insert the data in the bonus table.
  • Line 33-38: This is the SQLStructured Query Language query to extract an employee's first name who has a bonus amount of 5000.

Free Resources

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