SQL Server/T-SQL/Subquery/Subqueries ANY

ANY evaluates to true if the result of an inner query contains at least one row that satisfies the comparison

1>
2> CREATE TABLE works_on        (emp_no       INTEGER NOT NULL,
3>                         project_no    CHAR(4) NOT NULL,
4>                         job CHAR (15) NULL,
5>                         enter_date    DATETIME NULL)
6>
7> insert into works_on values (1, "p1", "analyst", "1997.10.1")
8> insert into works_on values (1, "p3", "manager", "1999.1.1")
9> insert into works_on values (2, "p2", "clerk",   "1998.2.15")
10> insert into works_on values (2, "p2",  NULL,     "1998.6.1")
11> insert into works_on values (3, "p2",  NULL,     "1997.12.15")
12> insert into works_on values (4, "p3", "analyst", "1998.10.15")
13> insert into works_on values (5, "p1", "manager", "1998.4.15")
14> insert into works_on values (6, "p1",  NULL,     "1998.8.1")
15> insert into works_on values (7, "p2", "clerk",   "1999.2.1")
16> insert into works_on values (8, "p3", "clerk",   "1997.11.15")
17> insert into works_on values (7, "p1", "clerk",   "1998.1.4")
18> GO
Msg 2714, Level 16, State 6, Server sqle\SQLEXPRESS, Line 2
There is already an object named "works_on" in the database.
1>
2> 
3>
4> -- ANY evaluates to true if the result of an inner query contains at least one row that satisfies the comparison
5>
6> SELECT DISTINCT emp_no, project_no, job FROM works_on WHERE enter_date > ANY
7>        (SELECT enter_date FROM works_on)
8> GO
emp_no      project_no job
----------- ---------- ---------------
          1 p3         manager
          2 p2         NULL
          2 p2         clerk
          3 p2         NULL
          4 p3         analyst
          5 p1         manager
          6 p1         NULL
          7 p1         clerk
          7 p2         clerk
          8 p3         clerk
(10 rows affected)
1>
2> drop table works_on
3> GO
1>
2>

Subqueries and ANY operator

29>
30> CREATE TABLE employee  (emp_no    INTEGER NOT NULL,
31>                         emp_fname CHAR(20) NOT NULL,
32>                         emp_lname CHAR(20) NOT NULL,
33>                         dept_no   CHAR(4) NULL)
34>
35> insert into employee values(1,  "Matthew", "Smith",    "d3")
36> insert into employee values(2,  "Ann",     "Jones",    "d3")
37> insert into employee values(3,  "John",    "Barrimore","d1")
38> insert into employee values(4,  "James",   "James",    "d2")
39> insert into employee values(5,  "Elsa",    "Bertoni",  "d2")
40> insert into employee values(6,  "Elke",    "Hansel",   "d2")
41> insert into employee values(7,  "Sybill",  "Moser",    "d1")
42> GO
(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
emp_no      emp_fname            emp_lname            dept_no
----------- -------------------- -------------------- -------
          1 Matthew              Smith                d3
          2 Ann                  Jones                d3
          3 John                 Barrimore            d1
          4 James                James                d2
          5 Elsa                 Bertoni              d2
          6 Elke                 Hansel               d2
          7 Sybill               Moser                d1
(7 rows affected)
1>
2> CREATE TABLE works_on        (emp_no       INTEGER NOT NULL,
3>                         project_no    CHAR(4) NOT NULL,
4>                         job CHAR (15) NULL,
5>                         enter_date    DATETIME NULL)
6>
7> insert into works_on values (1, "p1", "analyst", "1997.10.1")
8> insert into works_on values (1, "p3", "manager", "1999.1.1")
9> insert into works_on values (2, "p2", "clerk",   "1998.2.15")
10> insert into works_on values (2, "p2",  NULL,     "1998.6.1")
11> insert into works_on values (3, "p2",  NULL,     "1997.12.15")
12> insert into works_on values (4, "p3", "analyst", "1998.10.15")
13> insert into works_on values (5, "p1", "manager", "1998.4.15")
14> insert into works_on values (6, "p1",  NULL,     "1998.8.1")
15> insert into works_on values (7, "p2", "clerk",   "1999.2.1")
16> insert into works_on values (8, "p3", "clerk",   "1997.11.15")
17> insert into works_on values (7, "p1", "clerk",   "1998.1.4")
18> GO
Msg 2714, Level 16, State 6, Server sqle\SQLEXPRESS, Line 2
There is already an object named "works_on" in the database.
1>
2> select * from works_on
3> GO
emp_no      project_no job             enter_date
----------- ---------- --------------- -----------------------
          1 p1         analyst         1997-10-01 00:00:00.000
          1 p3         manager         1999-01-01 00:00:00.000
          2 p2         clerk           1998-02-15 00:00:00.000
          2 p2         NULL            1998-06-01 00:00:00.000
          3 p2         NULL            1997-12-15 00:00:00.000
          4 p3         analyst         1998-10-15 00:00:00.000
          5 p1         manager         1998-04-15 00:00:00.000
          6 p1         NULL            1998-08-01 00:00:00.000
          7 p2         clerk           1999-02-01 00:00:00.000
          8 p3         clerk           1997-11-15 00:00:00.000
          7 p1         clerk           1998-01-04 00:00:00.000
(11 rows affected)
1>
2> SELECT emp_fname, emp_lname FROM employee WHERE emp_no = ANY
3>        (SELECT emp_no FROM works_on WHERE project_no = "p1")
4> GO
emp_fname            emp_lname
-------------------- --------------------
Matthew              Smith
Elsa                 Bertoni
Elke                 Hansel
Sybill               Moser
(4 rows affected)
1>
2> drop table employee
3> GO
1>
2>