EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL ALL

SQL ALL

Priya Pedamkar
Article byPriya Pedamkar

Updated March 10, 2023

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax and Parameters

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

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.

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

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

We hope that this EDUCBA information on “SQL ALL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Clauses
  2. EXPLAIN in SQL
  3. SQL Server Data Types
  4. SQL Minus
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • 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

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW