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>