Correlated Sub Queries with Aliases

I know I’ve covered sub queries before, but I wanted to make sure I made a specific point. When you create a correlated sub query, you can reference columns from your subquery anywhere in your outer query. Usually you’ll either use it in your SELECT clause or your WHERE clause, but you can use it anywhere you’d use a column.

You just need to make sure you alias your tables, so you can properly identify which column you’re trying to reference.

One quick note: you can run these queries against your AdventureWorks database, the 2008 version.

Check out this query:

SELECT DISTINCT
	pv1.ProductID, pv1.BusinessEntityID
FROM Purchasing.ProductVendor pv1
WHERE pv1.ProductID IN
    (SELECT pv2.ProductID
     FROM Purchasing.ProductVendor pv2
     WHERE pv1.BusinessEntityID <> pv2.BusinessEntityID)
ORDER  BY pv1.BusinessEntityID

In this query we only used the pc2.productID in the inner query. You should be used to that by now. Here’s something you may not have known you could do.

SELECT DISTINCT
	pv1.ProductID, innerQuery.BusinessEntityID
FROM Purchasing.ProductVendor pv1
INNER JOIN (
  SELECT pv2.ProductID, pv2.BusinessEntityID
  FROM Purchasing.ProductVendor pv2) innerQuery
  ON pv1.ProductID = innerQuery.productID
ORDER  BY pv1.BusinessEntityID

In this query we got the BusinessEntityID value from the innerQuery. I know, this is a contrived example, but I wanted to show you how to use aliases with correlate sub queries, and how to reference them all.

I’ll be honest, I still get tripped up by aliasing every once in a while. When you get an odd result from your queries, chances are you can look at your alias references, and find the one you’ve missed. When that fails, always have someone you can bounce your queries off of for a sanity check. Otherwise, you may need a short vacation to Beautiful Arkham, the gardens really are lovely this time of year!

As always, let me know if you have any questions!

Tags:

No comments yet.

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.