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
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.
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.
FROM product p
WHERE p.product_id =
(SELECT o.product_id FROM order_items o WHERE o.product_id = p.product_id);
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
WHERE dept_id IS NOT NULL
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.
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%')
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
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)
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
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 BlogWHERE Username = 'Robert'
AND Username NOT IN
(SELECT username FROM Comments
WHERE Comment LIKE '%SQL%');
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’
(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.
Importing and using Excel data into MS SQL database
SQL Running Totals
Using Common Table Expressions (CTE) in MS SQL
Pivoting your data in MS SQL. Creating "Cross-Tab" reports.
Technorati Tags: MS SQL,Subquery,Select,MSSQL,T-SQL
0 comment(s) so far...
Do you want to receive blog updates via e-mail. Then just click on the link below. You will be redirected to Google's feed burner, where you can fill out a form. Supplying your e-mail address.
The subscription is managed entirely by Google's Feedburner. We cannot and do not collect your email address.
Subscribe to The Brave Programmer by Email
Sign up with Disqus to enjoy a surprise box of features