Home > Articles > Data > SQL

  • Print
  • + Share This
From the author of

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
  • + Share This
  • 🔖 Save To Your Account