SQL Server/T-SQL/Subquery/Subqueries ALL

Subqueries and ALL operator

11>
12> CREATE TABLE works_on       (emp_no       INTEGER NOT NULL,
13>                         project_no    CHAR(4) NOT NULL,
14>                         job CHAR (15) NULL,
15>                         enter_date    DATETIME NULL)
16>
17> insert into works_on values (1, "p1", "analyst", "1997.10.1")
18> insert into works_on values (1, "p3", "manager", "1999.1.1")
19> insert into works_on values (2, "p2", "clerk",   "1998.2.15")
20> insert into works_on values (2, "p2",  NULL,     "1998.6.1")
21> insert into works_on values (3, "p2",  NULL,     "1997.12.15")
22> insert into works_on values (4, "p3", "analyst", "1998.10.15")
23> insert into works_on values (5, "p1", "manager", "1998.4.15")
24> insert into works_on values (6, "p1",  NULL,     "1998.8.1")
25> insert into works_on values (7, "p2", "clerk",   "1999.2.1")
26> insert into works_on values (8, "p3", "clerk",   "1997.11.15")
27> insert into works_on values (7, "p1", "clerk",   "1998.1.4")
28> 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 rows affected)
(1 rows affected)
1>
2> -- 
3>
4> SELECT job FROM works_on WHERE emp_no <= ALL
5>        (SELECT emp_no FROM employee)
6> GO
job
---------------
analyst
manager
(2 rows affected)
1> drop table works_on
2> GO
1>
2>