Skip to content

SQL 201-Non-Correlated Sub Queries

2011 June 6
tags: ,
by Shannon Lowder

It’s been a while since I’ve covered any material for my SQL 201 series, so I thought I’d jump back into it with non-correlated sub queries.  It’s a mouthful.  But it’s a really simple concept, you can join two queries that have absolutely nothing to do with each other into one statement.

I’d like you to look back at my Sub Query Fundamentals and not one, but two articles on correlated sub queries.  These should remind you what a sub query is, and how to use a correlated sub query.  Once you’ve read those, you’re more than ready for this article!

This is different from a UNION, EXCEPT or CROSS JOIN.  Though some of the examples of non-correlated sub queries could be accomplished with a UNION, EXCEPT, or CROSS JOIN.

Business Use One – the not in sub query

I’ve lost count of the number of times I’ve fell back to this query to find records in one table that don’t have shared values in another.  Let’s create a test table in adventureworks.

   1: SELECT top 10 

   2:     ContactID, FirstName, LastName

   3: INTO Example.person

   4: FROM Person.contact

   5:  

Now, let’s say we want to find all contacts in Person.Contact that don’t exist in Example.person.  We could use a left join along with a search for NULL values in the WHERE clause.  Or, we could use a sub query.

   1: SELECT 

   2:     ContactID, FirstName, LastName 

   3: FROM Person.Contact

   4: WHERE

   5:     ContactID NOT IN (

   6:         SELECT ContactID

   7:         FROM Example.Person )

   8:  

Since the Sub query doesn’t relate to any column in the outer query, we have a non-correlated subquery!

Business Use Two – slightly less contrived

Let’s say you want to see what positions have an average pay rate higher than the company average.  Basically, what job should you go for if you want to make more money?

First, look at the company’s average pay rate.

   1: SELECT AVG(Rate)

   2: FROM HumanResources.EmployeePayHistory

   3:  

Now We want to look at the average rate for all positions in the company.

   1: SELECT

   2:     e.Title, AVG(eph.rate) as averageTitleRate

   3:  FROM HumanResources.Employee e

   4:     --ON c.ContactID = e.ContactID

   5: INNER JOIN HumanResources.EmployeePayHistory eph

   6:     ON e.EmployeeID = eph.EmployeeID

   7: GROUP BY

   8:     e.Title

 

Now, I want to use the first query as a sub query to the first in the HAVING clause.  That way the second query will only return the records where the job’s average rate is greater than the company’s average rate.

   1: SELECT

   2:     e.Title, AVG(eph.rate) as averageTitleRate

   3:  FROM HumanResources.Employee e

   4:     --ON c.ContactID = e.ContactID

   5: INNER JOIN HumanResources.EmployeePayHistory eph

   6:     ON e.EmployeeID = eph.EmployeeID

   7: GROUP BY

   8:     e.Title

   9: HAVING 

  10:     AVG(eph.Rate) > (

  11:         SELECT AVG(Rate)

  12:         FROM HumanResources.EmployeePayHistory )

  13: ORDER BY

  14:     averageTitleRate DESC

  15:  

 

I also threw in an ORDER BY, so the results would be from the highest to lowest rate.  Looks like I want to be a CEO when I grow up!

 

That’s non correlated sub queries.  If you have any questions, let me know.  I’m here to help!

One Response leave one →

Trackbacks and Pingbacks

  1. SQL201-The Syllabus | Shannon Lowder

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS