Subquery ALL SQL Server Example: Subquery ALL modifier Example
Automate Creating a Slide Deck When Watching Video Tutorials with IdealAutomateExplorer
Premium (Not Free) Video Tutorials
Free Video Tutorials & Free Tools
Subquery ALL SQL Server Example: Subquery ALL modifier Example
Purpose: – Illustrates the SQL Server syntax for the subquery all modifier.
Syntax: WHERE expression comparison_operator [ANY | ALL] (subquery) – ALL in subquery means all rows in the subquery must meet the condition
-- SUBQUERY ALL -- PURPOSE: ALL in subquery means all rows in the subquery must meet the condition -- SYNTAX For Subquery ALL -- Statements that include a subquery usually take one of these formats: -- •WHERE expression [NOT] IN (subquery) -- •WHERE expression comparison_operator [ANY | ALL] (subquery) -- •WHERE [NOT] EXISTS (subquery) create table people( ID int, name varchar (20), height int ) GO create table countries( ID int, country varchar (20), averageheight int) GO insert into people (ID, name, height) values (1, 'Paul', 72) GO insert into people (ID, name, height) values (2, 'John', 69) GO insert into people (ID, name, height) values (3, 'Mark', 75) GO insert into people (ID, name, height) values (4, 'Steve', 65) GO insert into countries(ID, country, averageheight) values(1,'US',70) GO insert into countries(ID, country, averageheight) values(2,'Canada',69) GO insert into countries(ID, country, averageheight) values(3,'Mexico',67) GO insert into countries(ID, country, averageheight) values(4,'India',73) GO select * from people; GO select * from countries; GO -- The height of the people selected needs to be greater than the height -- of all of the countries SELECT p.ID,p.name FROM People p WHERE p.height > ALL (SELECT averageHeight FROM countries c) GO drop table people; drop table countries; GO |