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.
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.
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.
Now We want to look at the average rate for all positions in the company.
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.
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!