Usage of the SQL Select all criteria query

All syntax

scalar_expression {= | <> |!= | > | >= |!> | < | <= |!<} all (subquery)

Scalar_expression
Any valid expression.

{= | <> |!= | > | >= |!> | < | <= |!<}
comparison operator.

Subquery
Returns a subquery for a single-column result set. The data type of the returned column must be the same as the Scalar_expression data type.

A restricted SELECT statement in which the ORDER BY clause, the COMPUTE clause, and the INTO keyword are not allowed.

Instance

The following example creates a stored procedure that determines whether all the components in the ADVENTUREWORKS2008R2 database tutorial that have the specified SalesOrderID can be manufactured in the specified number of days. The example uses a subquery to create a list of daystomanufacture values for all components with a specific SalesOrderID, and then confirms that all daystomanufacture are within the specified number of days.

Copy

Use ADVENTUREWORKS2008R2;
Go

CREATE PROCEDURE daystobuild @OrderID int, @NumberOfDays int
As
IF
@NumberOfDays >= All
(
SELECT DaysToManufacture
From Sales.SalesOrderDetail
JOIN production.product
On Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE SalesOrderID = @OrderID
)
PRINT ' All items ' is manufactured in specified number of days or less. '
ELSE
PRINT ' Some items for this order cannot is manufactured in specified number of days or less. ';

Let's look at a complete example

1> CREATE TABLE Employee (
2> ID int,
3> name nvarchar (10),
4> Salary int)
5> Go
1>
2> CREATE TABLE Job (
3> ID int,
4> title nvarchar (10),
5> averagesalary int)
6> Go
1>
2>
3> INSERT into employee (ID, name, salary) VALUES (1, ' Jason ', 1234)
4> Go

(1 rows affected)
1> INSERT into employee (ID, name, salary) VALUES (2, ' Robert ', 4321)
2> Go

(1 rows affected)
1> INSERT into employee (ID, name, salary) VALUES (3, ' Celia ', 5432)
2> Go

(1 rows affected)
1> INSERT into employee (ID, name, salary) VALUES (4, ' Linda ', 3456)
2> Go

(1 rows affected)
1> INSERT into employee (ID, name, salary) VALUES (5, ' David ', 7654)
2> Go

(1 rows affected)
1> INSERT into employee (ID, name, salary) VALUES (6, ' James ', 4567)
2> Go

(1 rows affected)
1> INSERT into employee (ID, name, salary) VALUES (7, ' Alison ', 8744)
2> Go

(1 rows affected)
1> INSERT into employee (ID, name, salary) VALUES (8, ' Chris ', 9875)
2> Go

(1 rows affected)
1> INSERT into employee (ID, name, salary) VALUES (9, ' Mary ', 2345)
2> Go

(1 rows affected)
1>
2> insert INTO Job (ID, title, averagesalary) VALUES (1, ' Developer ', 3000)
3> Go

(1 rows affected)
1> insert INTO Job (ID, title, Averagesalary) VALUES (2, ' Tester ', 4000)
2> Go

(1 rows affected)
1> insert INTO Job (ID, title, Averagesalary) VALUES (3, ' Designer ', 5000)
2> Go

(1 rows affected)
1> insert INTO Job (ID, title, Averagesalary) VALUES (4, ' Programmer ', 6000)
2> Go

(1 rows affected)
1>
2>
3> SELECT * from employee;
4> Go
ID Name Salary
----------- ---------- -----------
1 Jason 1234
2 Robert 4321
3 Celia 5432
4 Linda 3456
5 David 7654
6 James 4567
7 Alison 8744
8 Chris 9875
9 Mary 2345

(9 rows affected)
1> select * from job,
2> go
id        & nbsp title      averagesalary
----------------------------------
           1 developer           3000
          2 tester               4000
          3 Designer             5000
           4 programmer          6000

(4 rows affected)
1>
2>
3>--If your subquery returns a scalar value, can use a comparison operator,
4>
5> SELECT E.id,e.name
6> from Employee E
7> WHERE e.salary > All (SELECT averagesalary from Job J)
8> Go
ID Name
----------- ----------
5 David
7 Alison
8 Chris

(3 rows affected)
1>
2>
3> drop table employee;
4> drop table job;
5> Go
1>