SQL ALL Operator

What is SQL ALL Operator and how to use it?
The ALL Operator in SQL is used to carry out the comparison between a single column with a range of values. The result obtained by this operator is in the boolean value. It returns TRUE if all the specified queries meet the condition. The ALL Operator can be used with SELECT, HAVING, and WHERE clauses.
  • Syntax of ALL Operator(with SELECT Statement)  –

SELECT ALL column1,column2,column3,...,columnN FROM table_name WHERE condition;

Demo Table – 

Product Table –

Category Region City Country Customer_ID Customer_name
Office Supplies Central Chicago United States SM-20950 Suzanne McNair
Technology East New York City United States AH-10465 Amy Hunt
Office Supplies East New York City United States AH-10465 Amy Hunt
Office Supplies East Dover United States EP-13915 Emily Phan
Office Supplies East Dover United States EP-13915 Emily Phan
Technology West Aurora United States TP-21565 Tracy Poddar
Office Supplies West Aurora United States TP-21565 Tracy Poddar
Office Supplies East Long Beach United States AR-10825 Anthony Rawles
Office Supplies West Pasadena United States HA-14920 Helen Andreada
Furniture West Pasadena United States HA-14920 Helen Andreada

Manager’s Table – 

Region Regional Manager
West Sadie Pawthorne
East Chuck Magee
Central Roxanne Rodriguez
South Fred Suzuki

Example – Consider the above table Demo table and retrieve all the CustomerName using the ALL Operator.

SELECT ALL [Customer_name] FROM [dbo].[Products];

The output after implementing the above ALL operator with SELECT statement –
Customer_name
Suzanne McNair
Amy Hunt
Amy Hunt
Emily Phan
Emily Phan
Tracy Poddar
Tracy Poddar
Anthony Rawles
Helen Andreada
Helen Andreada
  • Syntax of ALL Operator(with WHERE clause)  –

SELECT column1,column2,column3,...,columnN FROM table_name WHERE column1,column2,.. OPERATOR ALL (SELECT column1,column2,column3,...,columnN FROM table_name WHERE condition); Example – Consider the above table Demo table and retrieve all the CustomerName using the ALL Operator for those who have Region as “West”.

SELECT [Customer Name] , Region FROM [dbo].[Products] WHERE [Region] = ALL (SELECT Region FROM [dbo].[Products] WHERE Region ='West') ;

The output after implementing the above ALL operator with WHERE clause –

Customer_name Region
Tracy Poddar West
Tracy Poddar West
Helen Andreada West
Helen Andreada West

Other SQL topics to check out:

Follow us on TwitterFacebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.

If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.

#analytics #data #business #artificialintelligence #machinelearning #startup #deeplearning #deeplearning #datascience #ai #growth #dataanalytics #india #datascientist #powerbi #dataanalysis #tableau #SQL #businessanalytics #businessanalyst #businessandmanagement #dataanalyst #businessanalysis #analyst #analysis #powerbideveloper #powerbidesktop #letsviz