sql conditional subquery - ignore subquery if it returns null?

Question

I have a query with a joined subquery. If the subquery returns null, I want it to be ignored and I want to the rest of the query to work normally.

Currently I have something like:

SELECT a, b, c, d
FROM tblOne
JOIN tblTwo ON tblOne.a = tblTwo.a --this works fine
JOIN
    (SELECT a
    FROM tblThree) ON tblThree.a = tblOne.a

The problem is that if tblThree.a is null, the entire query returns null. So, I only want to use the subquery if tblThree.a is not null.

Can I do something with CASE or COALESCE, or some other way? Please give code examples.

do a left outer join to that subquery, if it didnt return anything it would be ignored — Jafar Kofahi
– Jafar Kofahi, Commented Sep 19, 2013 at 15:59
You aren't even selecting e or f -- why do you need that second join at all? — mr.Reband
– mr.Reband, Commented Sep 19, 2013 at 16:01
@mr.Reband That's just an example, not the full query. I'll edit the OP to avoid confusion. — LFurness
– LFurness, Commented Sep 19, 2013 at 16:05

lc. · Accepted Answer · 2013-09-19 15:59:34Z

6

Use a LEFT OUTER JOIN instead of an INNER JOIN. This will return all rows for the rest of the query, even if tblThree returns no matching rows. In this case the columns for tblThree will all be NULL.

Using your query (although I have added the required alias for the derived table):

SELECT a, b, c, d
FROM tblOne
INNER JOIN tblTwo ON tblOne.a = tblTwo.a
LEFT OUTER JOIN
(
    SELECT a, e, f
    FROM tblThree
) tblThree ON tblThree.a = tblOne.a

Note, as @491243 points out, the derived-table subquery here does not really make sense. You also most likely have an ambiguous column a in the SELECT clause. I'm guessing this is just an extrapolation of your real query though.

answered Sep 19, 2013 at 15:59

lc.

117k21 gold badges163 silver badges190 bronze badges

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

2 Comments

John Woo Over a year ago

actually you can eliminate the subquery since it does not contain any aggregation.

lc. Over a year ago

@491243 Agreed although this is most likely a simplification of the OP's real query. And you would probably (and most likely still do?) get column name collisions in the main SELECT clause.

Vulcronos · Accepted Answer · 2013-09-19 16:12:59Z

1

Try changing your third join to a LEFT JOIN.

LEFT JOIN
    (SELECT a, e, f
    FROM tblThree) ON tblThree.a = tblOne.a

Then if a is null you will still get the original rows prior to the attempted join. Another option would be to add

WHERE a IS NOT NULL

to your subquery to only return rows where a has a value.

edited Sep 19, 2013 at 16:12

answered Sep 19, 2013 at 16:01

Vulcronos

3,4563 gold badges19 silver badges25 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2013-09-19 16:03:42Z

I'm not sure what you mean by "if tblThree.a is null, the entire query returns null". A NULL value for a would affect one row, not all the results. Otherwise matching rows would still match.

The following might do what you want, though:

select a, b, c, d
from (SELECT a, b, c, d, count(tblThree.a) over () as nummatches, tblthree.a as t3a
      FROM tblOne JOIN
           tblTwo
           ON tblOne.a = tblTwo.a LEFT OUTER JOIN
           (SELECT a, e, f
            FROM tblThree
           ) tblThree
           ON tblThree.a = tblOne.a
    ) t
where nummatches = 0 or nummatches > 0 and t3a is not null;

This query counts the number of valid a values in tblThree. If there are none, then all are returned, otherwise only the matching ones are. By the way, you don't need the subquery for the table if you use table aliases with column names.

Christian Phillips · Accepted Answer · 2013-09-19 18:00:10Z

0

Try using EXISTS..

SELECT a, b, c, d
FROM tblOne, tblTwo
WHERE EXISTS(
SELECT a from tblThree
WHERE tblThree.a=tblOne.a) AND tblOne.a = tblTwo.a;

Dint verify this myself.. but, you got it, right?

edited Sep 19, 2013 at 18:00

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

answered Sep 19, 2013 at 17:42

Ketki

486 bronze badges

Collectives™ on Stack Overflow

sql conditional subquery - ignore subquery if it returns null?

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related