SQL ALL Operator

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:

• How to write SQL Select Statements
• Introduction to SQL for Data Scientists
• What is RDBMS (Relational Database Management System)?
• SQL WHERE Clause

Follow us on Twitter, Facebook, 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