Select multiple columns from a subquery in SQL Server

Question

I know how to select a field from subquery:

SELECT 
    ID, fck, f1, 
    (SELECT f2 FROM tbl2 Y WHERE Y.ID = T.fck) AS f2
FROM 
    tbl1 T

If I need to select two columns from the subquery, actually I do:

SELECT 
    ID, fck, f1, 
    (SELECT f2 FROM tbl2 Y WHERE Y.ID = T.fck) AS f2, 
    (SELECT f3 FROM tbl2 Y WHERE Y.ID = T.fck) AS f3
FROM 
    tbl1 T

There is a way to avoid two subqueries? Something like:

SELECT 
    ID, fck, f1, 
    (SELECT f2, f3 FROM tbl2 Y WHERE Y.ID = T.fck) AS f2, f3
FROM 
    tbl1 T

Database is SQL Server 2008 R2.

I know this simple example may be rewritten using JOINs, but there are real cases where there aren't equivalent forms using JOIN.

This is a simple join behavior. If you need TOP 1 - use @Giorgos Betsos approach — IVNSTN
– IVNSTN, Commented Feb 15, 2016 at 14:57

Giorgos Betsos · Accepted Answer · 2016-02-15 14:53:32Z

22

You can use OUTER APPLY:

SELECT t1.ID, t1.fck, t1.f1, t3.f2, t3.f3
FROM tbl1 AS t1
OUTER APPLY (
   SELECT f2, f3 
   FROM tbl2 AS t2 
   WHERE t2.ID = t1.fck) AS t3

The SELECT clause contains scalar values, so it can't handle subqueries returning more than field, or multiple records of one field.

answered Feb 15, 2016 at 14:53

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marco Marsala Over a year ago

Thank you. Looking for that since months. Will try it and give you my feedback.

Manngo Over a year ago

This got me out of trouble with an update query. I needed a correlated sub query to fetch a random row from another table, and I needed to use more than one column from the random row. Thanks

Mahefa Over a year ago

@Giorgos Betsos > And for mysql ?

SlimsGhost · Accepted Answer · 2016-02-15 14:56:12Z

2

A correlated subquery is used to get a single value, but you can simply turn your subquery into a join to do what you need:

SELECT T.ID, T.fck, T.f1, Y.f2, Y.f3
FROM tbl1 T
INNER JOIN tbl2 Y ON Y.ID = T.fck

As with any join, you also need to determine whether you need an INNER JOIN versus OUTER JOIN, and make sure you understand whether the relationship between tables is 1..1, 1..N, etc., but in general this is how to get multiple fields from a related table.

answered Feb 15, 2016 at 14:56

SlimsGhost

2,9091 gold badge12 silver badges16 bronze badges

Collectives™ on Stack Overflow

Select multiple columns from a subquery in SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related