Multiple joins on SQL with WHERE condition

Question

Here is my T-SQL statement for joining three tables. Is it possible to remove the WITH and make it as one statement instead?

WITH TMP AS 
(
    SELECT * 
    FROM A
    LEFT OUTER JOIN B ON A.id = B.id
    WHERE a.name = 'x' AND b.name = 'y'
)
SELECT * FROM TMP
LEFT OUTER JOIN C ON TMP.id = C.id
WHERE c.name = 'T'

Yes it is possible, but it looks like you converted LEFT JOIN to INNER JOIN (b.name = 'y' and c.name = 'T' ) — Sergey, Jul 14 '22 at 13:45
That CTE isn't actually valid, it would (minimally) have 2 columns with the name `id` and 2 columns with the name `name`, causing an error as each column within a CTE must have a unique name/alias. This also makes it ambiguous what column `TMP.id = C.id` would join to; is that `A.id` or `B.id`? Though due to your "`LEFT INNER JOIN`" the 2 columns should have the same value at least. — Thom A, Jul 14 '22 at 13:46
To demonstrate what @larnu is talking about, you can have multiple columns with the same name in a result set, as long as you don't reference them in a query. But even just doing a `select *` from the CTE counts as referencing those columns. See it here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c1d9c8a27268fc73ad9acb720ace9e12 But I'm not too worried about it, as the query is obviously a contrived example. — Joel Coehoorn, Jul 14 '22 at 13:53
Indeed, @JoelCoehoorn . The point would be *less moot* if the `LEFT OUTER JOIN`s weren't implicit `INNER JOIN`s, as then it certainly would be ambiguous if it should be `A.id = C.id` or `B.id = C.id`. — Thom A, Jul 14 '22 at 13:55
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jul 15 '22 at 00:13

Joel Coehoorn · Answer 1 · 2022-07-14T13:56:29.243

3

SELECT * 
FROM A
LEFT JOIN B ON A.id = B.id
LEFT JOIN C ON C.id = A.id
WHERE a.name = 'x' AND b.name = 'y' AND c.name = 'T'

But considering the b.name = 'y' and c.name = 'T' conditions require rows from those tables to match the initial A table (you can never have nulls here), you would do just as well or better like this:

SELECT * 
FROM A
INNER JOIN B ON A.id = B.id
INNER JOIN C ON C.id = A.id
WHERE a.name = 'x' AND b.name = 'y' AND c.name = 'T'

edited Jul 14 '22 at 13:56

answered Jul 14 '22 at 13:47

Joel Coehoorn

399,467
113
570
794

5

Probably should switch to `INNER JOIN`s, considering the `WHERE` clause, or move the clauses on `b.name` and `c.name` to the `ON`s (I'd suggest the former based on the OP's attempt). – Thom A Jul 14 '22 at 13:49

Multiple joins on SQL with WHERE condition

1 Answers1