EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL ALL

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL ALL

SQL ALL

Introduction to SQL ALL

ALL operator in standard query language(SQL) is used to select all the rows or records of a SELECT statement. It can further be used as a comparison operator to compare values of a column to all the values obtained in the result set of a subquery in WHERE or HAVING clause of a SQL query. The ALL operator must be preceded by comparison operators such as >,<,=, etc. It returns TRUE if all the values obtained from the subquery meet the comparison condition.

Syntax and Parameters

The basic syntax for using ALL operator with SELECT statement is as follows :

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

SELECT ALL column_name
FROM table_name
WHERE condition(s);

The basic syntax for using ALL operator in WHERE clause is as follows :

SELECT column_name1, column_name2
FROM table_name
WHERE column_name comparison_operator ALL
(SELECT column_name
FROM table_name
WHERE condition_expression );

Parameters

The parameters used in the above-mentioned syntaxes is as follows :

column_name1, column_name2, … : fields or columns that have to be fetched in the final result set.

table_name: Name of the database table from which the said columns will be fetched from.

column_name: column which has to be used as a part of WHERE condition for comparison.

Popular Course in this category
JDBC Training (6 Courses, 7+ Projects)6 Online Courses | 7 Hands-on Projects | 37+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (6,044 ratings)
Course Price

View Course

Related Courses
PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)SQL Training Program (7 Courses, 8+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

comparison_operator: comparison operators are a set of operators used to check conditions. We can choose from Equal to (=), not equal to(<>),  less than(<), greater than(>), less than or equal to (<=), greater than or equal to (>=).

(SELECT column_name FROM table_name WHERE condition_expression ): The values obtained from the result set of this subquery will be compared with the column_name.

The syntax for using ALL operator with a HAVING clause is similar to the WHERE clause. The only difference is HAVING is generally used with GROUP BY clauses.

Examples of SQL ALL

In order to illustrate the functionality of ALL operator in SQL, what could be better than trying a few examples on a dummy table. Ergo, let us create two database tables called “employee” and “departments” respectively.

We can use the following CREATE table statements to create these tables.

CREATE TABLE employee
(
employee_id integer,
employee_name character varying(255),
department_id character varying(255),
salary numeric,
highest_qualification character varying(255)
)

SQL ALL output 1

CREATE TABLE departments
(
department_id character varying(255),
department_name character varying(255),
location character varying(255),
manager_id integer
)

SQL ALL output 2

We have successfully created both the tables, namely “employee” and “departments”. Now with the help of the given INSERT queries given below, let us insert a few records in both the tables to work with.

(i) INSERT statement for inserting records in the employee table.

INSERT INTO public.employee(
employee_id, employee_name, department_id, salary, highest_qualification)
VALUES (101,'Roy Bernard','D01',5000,'B.Sc'),
(102,'Gina Messenger','D01',6200,'M.Sc'),
(105,'Jim Perkins','D03',5000,'B.A'),
(106,'Erica Silverman','D03',7000,'MBA'),
(107,'Priyanka M','D01',5000,'B.Tech');

(ii) INSERT statement for inserting records in departments table.

INSERT INTO public.departments(
department_id, department_name, location, manager_id)
VALUES ('D01','Research','Singapore',102),
('D02','Human Resource','Santa Monica',104),
('D003','Sales','New York',106);

Now we are all set to try a few examples based on these tables.

Example #1 – ALL operator with SELECT statement

Show the list of all the employees depicting their employee_id and names.

Code:

SELECT ALL employee_id, employee_name
FROM employee
WHERE department_id = 'D003';

Output:

SQL ALL output 3

Example #2 – ALL operator with WHERE clause

Find the employee_ids and salaries of employees who earn less than or equal to all the employees in the ‘D003’ department.

Code:

SELECT employee_id, salary
FROM employee
WHERE salary <= ALL(SELECT salary
FROM employee
WHERE department_id = 'D003');

Output:

SQL ALL output 4

Example #3

Find the employee_id, salary, and highest qualification of employees who earn equal to all the managers in the company’s New York office.

SELECT employee_id, salary, highest_qualification
FROM employee
WHERE employee_id = ALL(SELECT manager_id
FROM departments
WHERE location = 'New York');

Output:

SQL ALL output 5

Example #4 – ALL operator with HAVING clause

Prepare a summary table consisting of total employees and average salaries grouped together by highest qualification, provided that salaries of these employees is more than the average salary of all the departments.

Code:

SELECT highest_qualification,
count(employee_id) as "Total_employees",
ROUND(AVG(salary),2) as "Average_salary"
FROM employee
GROUP BY highest_qualification
HAVING AVG(salary) > ALL( SELECT AVG(salary)
FROM departments as d
JOIN employee as e
ON d.department_id = e.department_id
);

Output:

SQL ALL output 6

Example #5 – ALL operator with the UPDATE statement

Suppose the company has decided to raise the salaries of employees who have been earning a minimum salary until now to $5100. Write an update query to perform this task.

Code:

UPDATE employee
SET salary = 5100
WHERE salary <= ALL(SELECT MIN(salary)
FROM employee
);

Output:

output 7

The query returned successfully. Let us check using the following SELECT query if the desired changes have been made.

SELECT * FROM employee;

Output:

output 8

It can be observed from the image that the salaries of employees who have been earning $5000 have been updated to $5100.

ALL operator with the DELETE statement

DELETE FROM employee
WHERE employee_id = ALL( SELECT manager_id
FROM departments
WHERE department_id = 'D01');

Output:

output 9

Use the following SELECT query to check if the desired rows have been deleted.

SELECT * FROM employee;

Output:

output 10

The query returned successfully and has deleted the details of department D01’s manager.

Conclusion

ALL is a comparison operator that returns TRUE if all the values in the result set obtained from a subquery meet the specified condition. The operator can be used along with a SELECT statement, WHERE and HAVING clause.

Recommended Articles

This is a guide to SQL ALL. Here we discuss the introduction to SQL ALL with appropriate syntax, respective parameters and query examples. You may also have a look at the following articles to learn more –

  1. SQL Clauses
  2. EXPLAIN in SQL
  3. SQL Server Data Types
  4. SQL Minus

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • SQL Window Functions
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • ANY in SQL
    • LIKE Query in SQL
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • SQL Ranking Function
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL INSTR()
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - JDBC Training Course Learn More