The Brave Programmer - Blogging and coding
Not for the faint hearted
 

Blog Posts From The Brave Programmer

Minimize

Using the SQL Subquery or Subselect

Jul13

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

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.

Related reading:

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: ,,,,

Tags:
Categories:
blog comments powered by Disqus
 
Blog Updates Via E-mail
 Blog Updates Via E-mail
Minimize

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

Print  
 

 

Latest Comments
 Latest Comments
Minimize
Powered by Disqus

Sign up with Disqus to enjoy a  surprise box of features

Print  
 
Blog Roll
 Blog Roll
Minimize
Print  
 
Categories
 Categories
Minimize
Print  
 
<h1>Search Blogs From The Brave Programmer</h1>
 

Search Blogs From The Brave Programmer

Minimize
Print  
 
Archive
 Archive
Minimize
Archive
<November 2014>
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456
Monthly
Go
Print  
 
<h1>News Feeds (RSS)</h1>
 

News Feeds (RSS)

Minimize

Mon, 26 May 2014 13:39:24 -0500

Mon, 19 May 2014 13:10:38 -0500

Tue, 13 May 2014 05:00:08 -0500

Mon, 12 May 2014 05:00:43 -0500

Sun, 11 May 2014 05:00:26 -0500

Tue, 06 May 2014 05:00:02 -0500

Mon, 05 May 2014 05:00:34 -0500

Fri, 02 May 2014 05:00:52 -0500

Thu, 01 May 2014 05:00:00 -0500

Fri, 18 Apr 2014 05:00:38 -0500

Print  
 

Follow robertbravery on Twitter

Blog Engage Blog Forum and Blogging Community, Free Blog Submissions and Blog Traffic, Blog Directory, Article Submissions, Blog Traffic

View Robert Bravery's profile on LinkedIn

Mybyte

 

Robert - Find me on Bloggers.com

Tags
 Tags
Minimize
Print  
 
Contact Us Now
 Contact Us Now
Minimize
 

Email  us now or call us on 082-413-1420,  to host your website.

We design and develop websites. We develop websites that make a difference. We do Dotnetnuke Module development.

Web Masters Around The World
Power By Ringsurf
Print