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.

SELECT top 10 
    ContactID, FirstName, LastName
INTO Example.person
FROM Person.contact
  

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.

SELECT 
    ContactID, FirstName, LastName 
FROM Person.Contact
WHERE
    ContactID NOT IN (
        SELECT ContactID
        FROM Example.Person )
  

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.

SELECT AVG(Rate)
FROM HumanResources.EmployeePayHistory
  

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

SELECT
    e.Title, AVG(eph.rate) as averageTitleRate
 FROM HumanResources.Employee e
    --ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeePayHistory eph
    ON e.EmployeeID = eph.EmployeeID
GROUP BY
    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.

SELECT
    e.Title, AVG(eph.rate) as averageTitleRate
 FROM HumanResources.Employee e
    --ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeePayHistory eph
    ON e.EmployeeID = eph.EmployeeID
GROUP BY
    e.Title
HAVING 
    AVG(eph.Rate) > (
        SELECT AVG(Rate)
        FROM HumanResources.EmployeePayHistory )
ORDER BY
    averageTitleRate DESC
  

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!

SQL 201-Non-Correlated Sub Queries

Business Use One – the not in sub query

Business Use Two – slightly less contrived

Leave a Reply Cancel reply