DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use select from select and correlated subqueries?

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  [email protected]    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  [email protected]    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-delimited 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?

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE