Subqueries (Part 1)

Good afternoon everyone! Today's blog post will be an introduction to the use of Subqueries in SQL Server. I have seen their use around projects that we have done in the workplace and they are actually quite flexible (though you have to be careful where you use them as they can reduce the speed and performance of the entire query).

Due to the nature of subqueries - I will start off with a brief introduction in this blog post and then explain in more detail about their uses within more complex systems over the coming days (stay tuned!)

So What Are Subqueries? 

Subqueries are basically queries that can return scalar values, multiple values or even a table of results nested within each other (with a maximum of 32 queries nested together). You may hear subqueries referred to as inner queries (with an outer query encapsulating) or as an inner SELECT. They can be used within any DML statement (SELECT, UPDATE, DELETE and INSERT) though they must contain a SELECT and a FROM clause which is declared within parentheses. What should also be noted is that they can be placed within the DML statement, either in the FROM clause or the WHERE clause, making them incredibly flexible.

There are 2 main types of subquery, Self-Contained (or Non-Correlated) and Correlated, which I am going to discuss below:

Self-Contained 

Self-Contained Queries are queries that do not rely on the outer query in order to bring back results. So basically, if you take everything inside the parentheses and execute it as a different query it would also bring back its own result set.

The example below shows a Self-Contained query that brings back the ItemId, ItemName and Price of the lowest priced Item in the Item table. You will be able to run both ends of the query and it will still bring back a result set:

Correlated

Correlated Queries, unlike Self-Contained queries, do rely on the outer query in order to bring back results, otherwise you will receive some form of error when trying to execute the inner query on its own. This is because, although it may not look like it at a first glance, there is actually a join within the subquery linking it to information from the outer query.

The example below shows a Correlated query that brings back all columns from the Customer table where a customer hasn't made an order (as the CustomerId won't feature in the CustomerItem Table):







An important thing to note is that you don't always use joins when linking tables together as you may simply wish to join two instances of the same table. You also don't always need to use the JOIN statement to create a join (as in the example above) however I prefer using them as it can make the code more readable.

Next...

Over the next coming days, I will explain more complex uses of subqueries, such as in Table Expressions where subqueries return result sets that have to be named and relational. These make excellent building blocks for more complicated queries as subqueries can be reused over and over again without having to be copied and pasted.

Also, I want to thank you all so much for reading and following my SQL-Genius blog, I really appreciate your support! If you have any comments, feedback or questions you wish to ask me please don't hesitate to message me in one of my comment boxes, or follow me on Facebook for regular updates.