Blog - Using the SQL Subquery or Subselect

The Brave Programmer - Blogging and coding

Not for the faint hearted

	Blog Posts From The Brave Programmer

Using the SQL Subquery or Subselect

Jul13

Written by:
2009/07/13 04:11 AM

Subqueries are an alternate way of returning data from multiple tables. When a SELECT statement is used inside another statement, the inside SELECT statement is known as a subquery. Also known as subselect, nested queries, inner query. Subqueries can help to dynamically control the records affected by an UPDATE, DELETE or INSERT statement, as well as to help determine the records that will be returned by a SELECT statement

What is a Subquery?

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement is executed independently of the T-SQL statement, in which it is nested. Basically a subquery can be used anywhere an expression can be used.

The thing to note is that a subquery SELECT statement can be a standalone query and is not depended on the statement in which it is nested. Let's look at a simple example before we carry on.

The following query is a stand alone query returning the MAX or highest value of a given comlumn:

SELECT MAX(day_of_order) FROM orders

We can use the following query to return a list of orders:

SELECT * FROM orders

But now we want to return a list of orders who's day_of_order is highest or the newest order date. We can achieve this by combining the first two queries in a subselect or subquery like so:

SELECT * FROM orders
WHERE day_of_order = (SELECT MAX(day_of_order) FROM orders)

This essentially is a good example of a subquery.

Correlated Subquery

A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

SELECT p.product_name
FROM product p
WHERE p.product_id =
(SELECT o.product_id FROM order_items o WHERE o.product_id = p.product_id);

Non-Correlated Subquery

A non-correlated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query. Non-correlated subqueries are executed once for the whole statement. The following example shows te use of a non-correlated subquery.

SELECT dept.name FROM dept
WHERE dept.id NOT IN
(
SELECT dept_id
FROM emp
WHERE dept_id IS NOT NULL
)

Uses of the Subquery

There are many uses for the subquery. But with all sql statements, one particular usage might not be the best. You could probably write a lot of subqueries as joins, and vice versa. The trick is to use the best method for any given situation. Below I will show some examples of the subquery use.

Subquery Search within a result set

One way to use this subquery functionality is by locating a value related by column with comparable data in two tables. Consider the following example:

SELECT blog_post_title from Blogs
WHERE author_name in
(SELECT comment_name from blog_comments WHERE comments LIKE '%Rob%')

Subquery in the Column List of a SELECT Statement

You can use a subquery in a column list to return a list of data as a column of the original query, but that data comming from a different table. Granted you could probably do many of these as joins, but for the sake of example lets look at the following:

SELECT o.CustNo, SUM(o.ItemsTotal), (SUM(o.ItemsTotal) /
(SELECT SUM(o1.ItemsTotal) FROM orders o1)) * 100

Subquery in the WHERE clause

A subquery can be used to control the records returned from a SELECT by controlling which records pass the conditions of a WHERE clause. In this case the results of the subquery would be used on one side of a WHERE clause condition. Here is an example:

SELECT distinct country from Northwind.dbo.Customers
WHERE country not in (select distinct country from Northwind.dbo.Suppliers)

Subquery in the FROM clause

The FROM clause normally identifies the tables used in the T-SQL statement. You can think of each of the tables identified in the FROM clause as a set of records. Well, a subquery is just a set of records, and therefore can be used in the FROM clause just like a table. Here is an example where a subquery is used in the FROM clause of a SELECT statement:

SELECT au_lname, au_fname, title
FROM (SELECT au_lname, au_fname, id FROM pubs.dbo.authors WHERE state = 'CA') as a
JOIN pubs.dbo.titleauthor ta on a.au_id=ta.au_id
JOIN pubs.dbo.titles t on ta.title_id = t.title_id

Subquery results using NOT IN

You can use the NOT IN keyword to obtain results explicitly not contained in another result set. COnsider the following; returning all records from the blog that have a username of "Robert", but his username is not mentioned on any blog comments that mention SQL.

SELECT * from Blog
WHERE Username = 'Robert'
AND Username NOT IN
(SELECT username FROM Comments
WHERE Comment LIKE '%SQL%');

Subquery results using EXISTS

With SQL you can always access the same data through more than one avenue, and you need to match up (or correlate) your results to get a cross-section of values.

SELECT username FROM Blog
WHERE Username = ‘Robert’
AND EXISTS
(SELECT username FROM Comments
WHERE where addeddate > '2009-01-23');

There are just so many situations where you could use subqueries, too many to even mention as examples in this blog. But I hope you get the idea.