|
The following
Microsoft SQL Server T-SQL sample scripts illustrate the applicaton of select from selects (derived tables) and correlated subqueries to construct complex queries.
-- SQL select from select - subquery (not correlated) - derived table (p)
USE AdventureWorks;
SELECT *
FROM (SELECT *
FROM Production.Product) p
GO
/* Partial results
ProductID Name ProductNumber
1 Adjustable Race AR-5381
2 Bearing Ball BA-8327
3 BB Ball Bearing BE-2349
4 Headset Ball Bearings BE-2908
*/
-- SQL select from select - p is a derived table - subquery / subselect
SELECT ProductName = p.Name,
p.ListPrice
FROM (SELECT *
FROM Production.Product
WHERE ListPrice > 0) p
ORDER BY ListPrice DESC,
ProductName
GO
/* Partial results
ProductName ListPrice
Road-150 Red, 44 3578.27
Road-150 Red, 48 3578.27
Road-150 Red, 52 3578.27
Road-150 Red, 56 3578.27
Road-150 Red, 62 3578.27
Mountain-100 Silver, 38 3399.99
*/
-- SQL Server select from select - Outer select with a random sample of 5
SELECT TOP 5 *
FROM (SELECT SalesOrderID,
OrderDate = convert(VARCHAR,OrderDate,101),
Total = Subtotal
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2003
AND MONTH(OrderDate) = 2) soh
ORDER BY NEWID()
GO
/* Results
SalesOrderID OrderDate Total
49173 02/01/2003 2071.4196
49415 02/25/2003 2443.35
49219 02/05/2003 2049.0982
49170 02/01/2003 4903.775
49275 02/10/2003 2443.35
*/
-- SQL nested select from select - FLOOR math function
SELECT TOP 5 *
FROM (SELECT ProductName = p.Name,
ListPrice=FLOOR(p.ListPrice),
Color
FROM (SELECT *
FROM Production.Product
WHERE ListPrice > 0) p
WHERE Color IS NOT NULL) pp
ORDER BY NEWID()
GO
/* Results
ProductName ListPrice Color
Mountain-300 Black, 44 1079.00 Black
HL Mountain Frame - Silver, 42 1364.00 Silver
Mountain-100 Silver, 42 3399.00 Silver
HL Road Frame - Red, 48 1431.00 Red
Road-150 Red, 56 3578.00 Red
*/
------------
-- Find all sales staff who received 2,000.0 in bonus.
-- SQL correlated subquery where filter
SELECT SalesPerson = c.LastName + ', ' + c.FirstName,
EmailAddress,
Phone
FROM HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
-- subquery below
WHERE 2000.00 = (SELECT Bonus
FROM Sales.SalesPerson sp
-- This is the correlationship
WHERE e.EmployeeID = sp.SalesPersonID);
GO
/* SalesPerson EmailAddress Phone
Mitchell, Linda linda3@adventure-works.com 883-555-0116
*/
-- Find all sales staff who received 2,000.0 in bonus.
-- SQL derived table - select from select
SELECT SalesPerson = c.LastName + ', ' + c.FirstName,
EmailAddress,
Phone
FROM HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
-- sp is the derived table
INNER JOIN (SELECT SalesPersonID,
Bonus
FROM Sales.SalesPerson sp) sp
-- The correlationship made into JOIN
ON e.EmployeeID = sp.SalesPersonID
WHERE 2000.00 = sp.Bonus
GO
/* SalesPerson EmailAddress Phone
Mitchell, Linda linda3@adventure-works.com 883-555-0116
*/
------------
-- Find all employees working on job_id 7
USE pubs;
-- SQL correlated query - subselect
-- SQL correlated subquery
-- SQL subselect
SELECT EmployeeID = emp_id,
FirstName = fname,
LastName = lname,
JobID = job_id,
(SELECT Count(* )
FROM employee e2
WHERE e2.lname <= e1.lname
AND e2.job_id = 7) AS SeqNo
FROM employee e1
WHERE job_id = 7
ORDER BY LastName
GO
/* Results
EmployeeID FirstName LastName JobID SeqNo
L-B31947F Lesley Brown 7 1
PDI47470M Palle Ibsen 7 2
M-L67958F Maria Larsson 7 3
HAN90777M Helvetius Nagy 7 4
*/
-- Find all publishers who actually published titles (books)
-- SQL correlated subquery
-- SQL derived table
-- Query with derived table in correlated subquery
SELECT p.*
FROM publishers p
-- Correlated subquery
WHERE pub_id IN (SELECT pub_id
-- Derived table x
FROM (SELECT pub_id
FROM titles) x
WHERE x.pub_id = p.pub_id)
/* Results
pub_id pub_name city state country
0736 New Moon Books Boston MA USA
0877 Binnet & Hardley Washington DC USA
1389 Algodata Infosystems Berkeley CA USA
*/
-- List all staff who works or worked in a particular territory
-- SQL xml path
-- SQL create comma-limited list
-- SQL correlated subquery
USE AdventureWorks;
SELECT Territory = st.[Name],
SalesYTD = '$' + Convert(VARCHAR,Floor(SalesYTD),1),
-- Correlated subquery
Staff = Stuff((SELECT ', ' +
c.FirstName + ' ' + c.LastName AS [text()]
FROM Person.Contact c
INNER JOIN Sales.SalesTerritoryHistory sth
ON c.ContactID = sth.SalesPersonID
-- correlation to outer query
WHERE sth.TerritoryID = st.TerritoryID
ORDER BY StartDate
For XML Path ('')),1,1,'')
FROM AdventureWorks.Sales.SalesTerritory st
ORDER BY SalesYTD DESC;
GO
/* Partial results
Territory SalesYTD Staff
Northwest $9,178,765.00 Shannon Elliott, Terry Eminhizer, Martha Espinoza
Southwest $8,351,296.00 Shelley Dyck, Jauna Elson
Canada $6,917,270.00 Carla Eldridge, Michael Emanuel, Gail Erickson
Central $4,677,108.00 Linda Ecoffey, Maciej Dusza
*/
Related articles:
Correlated Subqueries
How to use derived tables?
|
