470,874 Members | 1,705 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Subqueries in SQL

NeoPa
32,311 Expert Mod 16PB
To use a subquery (SQ) in SQL there are two ways :
  1. Treating the SQ as a record source (like a table).
  2. Treating the SQ as a single value.
In either case, the SQ is a simple SELECT query but surrounded by parentheses ().
In case 1 the SQ should either be in the FROM clause or, sometimes possible put within the In() command.
In case 2 the SQ can be used in place of any other item that returns a value (SELECT; WHERE; HAVING; GROUP BY; etc).

Assume the following structure :
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblOKData
  2. ID; Autonumber; PK
  3. Name; String
with records :
Expand|Select|Wrap|Line Numbers
  1. 1   Bat
  2. 2   Ball
  3. 3   Racquet
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblALLData
  2. ID; Autonumber; PK
  3. Name; String
with records :
Expand|Select|Wrap|Line Numbers
  1. 1   Glass
  2. 2   Cup
  3. 3   Plate
  4. 21  Bat
  5. 22  Ball
  6. 23  Racquet
Using a SQ within In().
Say that we wanted to show the tblAllData.ID for all items whose names match those found in the tblOKData table. We could use an INNER JOIN in this case, but alternatively (necessary to illustrate the point here) we could do it with a SQ.
The code would be :
Expand|Select|Wrap|Line Numbers
  1. SELECT ID
  2. FROM tblAllData
  3. WHERE Name In(SELECT [Name]
  4.               FROM tblOKData)
Using a SQ within the FROM clause as a Recordset.
The simplest form of this is to surround a basic SELECT query in parentheses and rename (AS {Name}).
In this case we want the same effect as the SQL above.
The code would be :
Expand|Select|Wrap|Line Numbers
  1. SELECT subQ.ID
  2. FROM (SELECT *
  3.       FROM tblAllData) AS subQ INNER JOIN tblOKData
  4.   ON subQ.Name = tblOKData.Name
Using a SQ as a Simple Value.
We want to select all tblAllData.IDs which are greater than the average value of these IDs.
The code would be :
Expand|Select|Wrap|Line Numbers
  1. SELECT [ID]
  2. FROM tblAllData
  3. WHERE [ID]>(SELECT Avg([ID])
  4.             FROM tblAllData)
Attached Files
File Type: zip SubQueries.Zip (8.6 KB, 721 views)
Jan 26 '07 #1
0 13715

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by pete | last post: by
6 posts views Thread by Daniel Elliott | last post: by
2 posts views Thread by Kevin | last post: by
2 posts views Thread by orin | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.