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>
Related examples in the same category