SQL Server/T-SQL/Subquery/Subqueries

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

A subquery can be used with comparison operators

   <source lang="sql">

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>

      </source>
   
  


A subquery is simply a SELECT query within a SELECT query

   <source lang="sql">

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>

      </source>
   
  


Return a single value from subquery

   <source lang="sql">

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>

      </source>
   
  


Subqueries and Comparison Operators

   <source lang="sql">

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>

      </source>