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:

	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.

	pv1.ProductID, innerQuery.BusinessEntityID
FROM Purchasing.ProductVendor pv1
  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!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *