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.

Answer 1

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.

Answer 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.