SQL Server/T-SQL/Subquery/Subqueries
Содержание
A subquery can be used with comparison operators
19>
20> CREATE TABLE employee (emp_no INTEGER NOT NULL,
21> emp_fname CHAR(20) NOT NULL,
22> emp_lname CHAR(20) NOT NULL,
23> dept_no CHAR(4) NULL)
24>
25> insert into employee values(1, "Matthew", "Smith", "d3")
26> insert into employee values(2, "Ann", "Jones", "d3")
27> insert into employee values(3, "John", "Barrimore","d1")
28> insert into employee values(4, "James", "James", "d2")
29> insert into employee values(5, "Elsa", "Bertoni", "d2")
30> insert into employee values(6, "Elke", "Hansel", "d2")
31> insert into employee values(7, "Sybill", "Moser", "d1")
32>
33> select * from employee
34> GO
Msg 2714, Level 16, State 6, Server sqle\SQLEXPRESS, Line 20
There is already an object named "employee" in the database.
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>
19> select * from works_on
20> 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)
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> -- A subquery can be used with other comparison operators
3>
4> SELECT DISTINCT project_no FROM works_on WHERE emp_no <
5> (SELECT emp_no FROM employee WHERE emp_lname = "Moser")
6> GO
project_no
----------
p1
p2
p3
(3 rows affected)
1>
2> drop table employee
3> drop table works_on
4> GO
1>
2>
A subquery is simply a SELECT query within a SELECT query
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> -- A subquery is simply a SELECT query within a SELECT query.
Subqueries scalar expressions.
3>
4> -- Scalar Expressions
5> SELECT City, Salary
6> , (SELECT AVG(Salary) FROM Employee)
7> AS AvgSalary
8> FROM Employee
9>
10>
11>
12>
13>
14> drop table employee
15> GO
City Salary AvgSalary
---------- ----------- -----------
New York 40420 49580
Vancouver 14420 49580
Toronto 24020 49580
New York 40620 49580
Vancouver 80026 49580
Toronto 70060 49580
New York 90620 49580
Vancouver 26020 49580
Toronto 60020 49580
(9 rows affected)
1>
Return a single value from subquery
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> SELECT ID, Name, Salary
3> , Salary - (SELECT Avg(Salary) FROM Employee)
4> As AvgSalaryDifference
5> FROM Employee
6>
7>
8>
9> drop table employee
10> GO
ID Name Salary AvgSalaryDifference
----------- ---------- ----------- -------------------
1 Jason 40420 -9160
2 Robert 14420 -35160
3 Celia 24020 -25560
4 Linda 40620 -8960
5 David 80026 30446
6 James 70060 20480
7 Alison 90620 41040
8 Chris 26020 -23560
9 Mary 60020 10440
(9 rows affected)
1>
Subqueries and Comparison Operators
15>
16> CREATE TABLE employee (emp_no INTEGER NOT NULL,
17> emp_fname CHAR(20) NOT NULL,
18> emp_lname CHAR(20) NOT NULL,
19> dept_no CHAR(4) NULL)
20> GO
1> insert into employee values(1, "Matthew", "Smith", "d3")
2> insert into employee values(2, "Ann", "Jones", "d3")
3> insert into employee values(3, "John", "Barrimore","d1")
4> insert into employee values(4, "James", "James", "d2")
5> insert into employee values(5, "Elsa", "Bertoni", "d2")
6> insert into employee values(6, "Elke", "Hansel", "d2")
7> insert into employee values(7, "Sybill", "Moser", "d1")
8> 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 department(dept_no CHAR(4) NOT NULL,
3> dept_name CHAR(25) NOT NULL,
4> location CHAR(30) NULL)
5>
6> insert into department values ("d1", "developer", "Dallas")
7> insert into department values ("d2", "tester", "Seattle")
8> insert into department values ("d3", "marketing", "Dallas")
9>
10> select * from department
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
dept_no dept_name location
------- ------------------------- ------------------------------
d1 developer Dallas
d2 tester Seattle
d3 marketing Dallas
(3 rows affected)
1>
2> -- Subqueries and Comparison Operators
3>
4> SELECT emp_fname, emp_lname FROM employee
5> WHERE dept_no = (SELECT dept_no FROM department WHERE dept_name = "marketing")
6> GO
emp_fname emp_lname
-------------------- --------------------
Matthew Smith
Ann Jones
(2 rows affected)
1>
2> drop table department
3> drop table employee
4> GO
1>
2>