SQL Server/T-SQL Tutorial/Query/ALL — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 10:23, 26 мая 2010

A SELECT statement that returns all rows

ALL keyword causes all rows matching the search condition to be included in the result set.
To use the ALL keyword, code it immediately after the SELECT keyword.
7>
8>
9> create table Bankers(
10>    BankerID             Integer,
11>    BankerName           VARCHAR(20),
12>    BankerContactLName   VARCHAR(20),
13>    BankerContactFName   VARCHAR(20),
14>    BankerCity           VARCHAR(20),
15>    BankerState          VARCHAR(20),
16>    BankerZipCode        VARCHAR(20),
17>    BankerPhone          VARCHAR(20)
18> )
19> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat",  "Paris",    "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina",   "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee",  "Toronto",  "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary",  "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act",  "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat",  "Orland",   "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee",  "Wisler",   "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee",  "Ticker",   "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2> SELECT ALL BankerCity, BankerState
3> FROM Bankers
4> ORDER BY BankerCity
5> GO
BankerCity           BankerState
-------------------- --------------------
Calgary              TY
New York             DE
Orland               PO
Paris                CA
Regina               ER
San Franc            FG
Ticker               MN
Toronto              YU
Vancouver            BC
Wisler               PL
(10 rows affected)
1>
2>
3> drop table Bankers;
4> GO
1>
2>
3>
4>


Finding the First Order for Each Customer Using the ALL Predicate

4>
5> CREATE TABLE employee(
6>    id          INTEGER NOT NULL PRIMARY KEY,
7>    first_name  VARCHAR(10),
8>    last_name   VARCHAR(10),
9>    salary      DECIMAL(10,2),
10>    start_Date  DATETIME,
11>    region      VARCHAR(10),
12>    city        VARCHAR(20),
13>    managerid   INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" ,  "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison",  "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" ,  "Smith",  6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" ,  "Rice",   5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert",  "Black",  4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" ,  "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" ,  "Larry",  5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" ,  "Cat",    4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan"  ,  "Act",    6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(9 rows affected)
1>
2>
3>
4> SELECT
5>   O1.*
6> FROM
7>     Employee AS O1
8> WHERE
9>     O1.ID <= ALL
10> (
11>   SELECT
12>     O2.ID
13>   FROM
14>       Employee AS O2
15>   WHERE
16>       O2.ID = O1.ID
17> )
18>
19>
20> drop table employee;
21> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(9 rows affected)


Finding the Last Row in Employee Using ALL

3>
4> CREATE TABLE employee(
5>    id          INTEGER NOT NULL PRIMARY KEY,
6>    first_name  VARCHAR(10),
7>    last_name   VARCHAR(10),
8>    salary      DECIMAL(10,2),
9>    start_Date  DATETIME,
10>    region      VARCHAR(10),
11>    city        VARCHAR(20),
12>    managerid   INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" ,  "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison",  "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" ,  "Smith",  6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" ,  "Rice",   5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert",  "Black",  4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" ,  "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" ,  "Larry",  5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" ,  "Cat",    4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan"  ,  "Act",    6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(9 rows affected)
1>
2>
3>
4> SELECT
5>   *
6> FROM
7>     Employee
8> WHERE
9>     ID >= ALL
10> (
11>   SELECT
12>     ID
13>   FROM
14>       Employee
15> )
16>
17>
18> drop table employee;
19> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(1 rows affected)


Finding the Most Recently Shipped Employee Using ALL

3>
4> CREATE TABLE employee(
5>    id          INTEGER NOT NULL PRIMARY KEY,
6>    first_name  VARCHAR(10),
7>    last_name   VARCHAR(10),
8>    salary      DECIMAL(10,2),
9>    start_Date  DATETIME,
10>    region      VARCHAR(10),
11>    city        VARCHAR(20),
12>    managerid   INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" ,  "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison",  "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" ,  "Smith",  6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" ,  "Rice",   5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert",  "Black",  4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" ,  "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" ,  "Larry",  5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" ,  "Cat",    4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan"  ,  "Act",    6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(9 rows affected)
1>
2>
3>
4> SELECT
5>   *
6> FROM
7>     Employee
8> WHERE
9>     Start_Date >= ALL(
10>  SELECT
11>    Start_Date
12>  FROM
13>      Employee
14> )
15>
16>
17> drop table employee;
18> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
(1 rows affected)
1>


Finding the Most Recently Shipped Employee Using ALL with NULL Handling

5> CREATE TABLE employee(
6>    id          INTEGER NOT NULL PRIMARY KEY,
7>    first_name  VARCHAR(10),
8>    last_name   VARCHAR(10),
9>    salary      DECIMAL(10,2),
10>    start_Date  DATETIME,
11>    region      VARCHAR(10),
12>    city        VARCHAR(20),
13>    managerid   INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" ,  "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison",  "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" ,  "Smith",  6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" ,  "Rice",   5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert",  "Black",  4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" ,  "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" ,  "Larry",  5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" ,  "Cat",    4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan"  ,  "Act",    6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(9 rows affected)
1>
2>
3> SELECT
4>   *
5> FROM
6>     Employee
7> WHERE
8>     Start_Date >= ALL
9> (
10>  SELECT
11>    Start_Date
12>  FROM
13>      Employee
14>  WHERE
15>      Start_Date IS NOT NULL
16> )
17>
18>
19>
20> drop table employee;
21> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
(1 rows affected)


How the ALL keyword works

Condition               Equivalent expression
x > ALL (1, 2)          x > 2
x < ALL (1, 2)          x < 1
x = ALL (1, 2)          (x = 1) AND (x = 2)
x <> ALL (1, 2)         (x <> 1) AND (x <> 2)


14>
15>
16>
17> create table Billings (
18>     BankerID           INTEGER,
19>     BillingNumber      INTEGER,
20>     BillingDate        datetime,
21>     BillingTotal       INTEGER,
22>     TermsID            INTEGER,
23>     BillingDueDate     datetime ,
24>     PaymentTotal       INTEGER,
25>     CreditTotal        INTEGER
26>
27> );
28> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3>
4> create table Bankers(
5>    BankerID             Integer,
6>    BankerName           VARCHAR(20),
7>    BankerContactLName   VARCHAR(20),
8>    BankerContactFName   VARCHAR(20),
9>    BankerCity           VARCHAR(20),
10>    BankerState          VARCHAR(20),
11>    BankerZipCode        VARCHAR(20),
12>    BankerPhone          VARCHAR(20)
13> )
14> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat",  "Paris",    "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina",   "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee",  "Toronto",  "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary",  "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act",  "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat",  "Orland",   "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee",  "Wisler",   "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee",  "Ticker",   "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3> SELECT BankerName, BillingNumber, BillingTotal
4> FROM Billings JOIN Bankers ON Billings.BankerID = Bankers.BankerID
5> WHERE BillingTotal > ALL
6>     (SELECT BillingTotal
7>     FROM Billings
8>     WHERE BankerID = 34)
9> ORDER BY BankerName
10> GO
BankerName           BillingNumber BillingTotal
-------------------- ------------- ------------
ABC Inc.                         1          165
DEF Inc.                         2          165
DFG Inc.                         9          165
GHJ Inc.                         0          165
HJI Inc.                         3          165
OIP Inc.                         7          165
QWE Inc.                         4          165
RTY Inc.                         5          165
SAD Inc.                         8          165
YUI Inc.                         6          165
(10 rows affected)
1>
2> drop table Billings;
3> drop table Bankers;
4> GO


The ALL operator evaluates to true if the evaluation of the table column in the inner query returns all values of that column.

Get jobs of the employee with the smallest employee number.
5>
6>
7> CREATE TABLE employee(
8>    id          INTEGER NOT NULL PRIMARY KEY,
9>    first_name  VARCHAR(10),
10>    last_name   VARCHAR(10),
11>    salary      DECIMAL(10,2),
12>    start_Date  DATETIME,
13>    region      VARCHAR(10),
14>    city        VARCHAR(20)
15> );
16> GO
1> INSERT INTO employee VALUES (1, "Jason" ,  "Martin", 5890,"2005-03-22","North","Vancouver");
2> INSERT INTO employee VALUES (2, "Alison",  "Mathews",4789,"2003-07-21","South","Utown");
3> INSERT INTO employee VALUES (3, "James" ,  "Smith",  6678,"2001-12-01","North","Paris");
4> INSERT INTO employee VALUES (4, "Celia" ,  "Rice",   5567,"2006-03-03","South","London");
5> INSERT INTO employee VALUES (5, "Robert",  "Black",  4467,"2004-07-02","East","Newton");
6> INSERT INTO employee VALUES (6, "Linda" ,  "Green" , 6456,"2002-05-19","East","Calgary");
7> INSERT INTO employee VALUES (7, "David" ,  "Larry",  5345,"2008-03-18","West","New York");
8> INSERT INTO employee VALUES (8, "James" ,  "Cat",    4234,"2007-07-17","West","Regina");
9> INSERT INTO employee VALUES (9, "Joan"  ,  "Act",    6123,"2001-04-16","North","Toronto");
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> select * from employee;
2> GO
id          first_name last_name  salary       start_Date              region     city
----------- ---------- ---------- ------------ ----------------------- ---------- --------------------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto
(9 rows affected)
1>
2> CREATE TABLE title(
3>    id  INTEGER,
4>    job_title VARCHAR(20)
5> );
6> GO
1>
2> INSERT INTO title VALUES (6, "coder");
3> INSERT INTO title VALUES (7, "tester");
4> INSERT INTO title VALUES (8, "developer");
5> INSERT INTO title VALUES (9, "manager");
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT *
4>        FROM title
5>        WHERE id <= ALL
6>        (SELECT id
7>          FROM employee)
8> GO
id          job_title
----------- --------------------
(0 rows affected)
1> drop table employee;
2> drop table title;
3> GO


The ALL predicate is more restrictive, in that all of the values inside the subquery must satisfy the comparison condition.

4>
5> CREATE TABLE employee(
6>    id          INTEGER NOT NULL PRIMARY KEY,
7>    first_name  VARCHAR(10),
8>    last_name   VARCHAR(10),
9>    salary      DECIMAL(10,2),
10>    start_Date  DATETIME,
11>    region      VARCHAR(10),
12>    city        VARCHAR(20),
13>    managerid   INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" ,  "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison",  "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" ,  "Smith",  6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" ,  "Rice",   5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert",  "Black",  4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" ,  "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" ,  "Larry",  5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" ,  "Cat",    4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan"  ,  "Act",    6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(9 rows affected)
1>
2>
3> SELECT
4>   *
5> FROM
6>     employee AS T
7> WHERE
8>     1 < ALL
9> (
10>   SELECT
11>     COUNT (*)
12>   FROM
13>       Employee
14>   where id = 10
15> )
16>
17>
18>
19>
20> drop table employee;
21> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
(0 rows affected)