␡
- Subquery Basics
- Subqueries in the FROM Clause
- Subqueries in the SELECT Clause
< Back
Page 3 of 3
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Subqueries in the SELECT Clause
Some systems allow subqueries in the SELECT statement, in which the subqueries act as SELECT list expressions. In the following query, you use SELECT-clause correlated subqueries to find principal, second, and third authors. Each subquery joins the outer table in the subquery WHERE clause. The subquery WHERE clause specifies the authors included in the subquery results.
Transact-SQL, Adaptive Server Anywhere
select distinct title_id,
(select au_id
from titleauthors
where au_ord = 1 and title_id = t.title_id) as first_author,
(select au_id
from titleauthors
where au_ord = 2 and title_id = t.title_id) as second_authors,
(select au_id
from titleauthors
where au_ord = 3 and title_id = t.title_id) as third_author
from titleauthors t
title_id first_author second_authors third_author
-------- ------------ -------------- ------------
BU1032 409-56-7008 213-46-8915 NULL
BU1111 724-80-9391 267-41-2394 NULL
BU2075 213-46-8915 NULL NULL
BU7832 274-80-9391 NULL NULL
MC2222 712-45-1867 NULL NULL
MC3021 722-51-5454 899-46-2035 NULL
PC1035 238-95-7766 NULL NULL
PC8888 427-17-2319 846-92-7186 NULL
PC9999 486-29-1786 NULL NULL
PS1372 756-30-7391 724-80-9391 NULL
PS2091 998-72-3567 899-46-2035 NULL
PS2106 998-72-3567 NULL NULL
PS3333 172-32-1176 NULL NULL
PS7777 486-29-1786 NULL NULL
TC3218 807-91-6654 NULL NULL
TC4203 648-92-1872 NULL NULL
TC7777 672-71-3249 267-41-2394 472-27-2349
(17 row(s) affected)
SELECT Subquery Alternatives
You can do the same kind of work, much more efficiently, with the CASE function. By avoiding multiple subqueries, you cut down on the number of times you have to read the table.
select title_id, min (case au_ord when 1 then au_id end) as A1, min (case au_ord when 2 then au_id end) as A2, min (case au_ord when 3 then au_id end) as A3 from titleauthors group by title_id order by title_id title_id A1 A2 A3 -------- ----------- ----------- ----------- BU1032 409-56-7008 213-46-8915 NULL BU1111 724-80-9391 267-41-2394 NULL BU2075 213-46-8915 NULL NULL BU7832 274-80-9391 NULL NULL MC2222 712-45-1867 NULL NULL MC3021 722-51-5454 899-46-2035 NULL PC1035 238-95-7766 NULL NULL PC8888 427-17-2319 846-92-7186 NULL PC9999 486-29-1786 NULL NULL PS1372 756-30-7391 724-80-9391 NULL PS2091 998-72-3567 899-46-2035 NULL PS2106 998-72-3567 NULL NULL PS3333 172-32-1176 NULL NULL PS7777 486-29-1786 NULL NULL TC3218 807-91-6654 NULL NULL TC4203 648-92-1872 NULL NULL TC7777 672-71-3249 267-41-2394 472-27-2349 (17 row(s) affected)
Another form of the CASE query looks like this:
select title_id, min (case when au_ord = 1 then au_id end) as A1, min (case when au_ord =2 then au_id end) as A2, min (case when au_ord = 3 then au_id end) as A3 from titleauthors group by title_id order by title_id
< Back
Page 3 of 3