Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

all
operator
sql
communitycreator

What is the ALL operator in SQL?

Salman Yousaf

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

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.

RELATED TAGS

all
operator
sql
communitycreator

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring