joining three tables between each other

Question

Is it possible to join three tables in this way .

select T1.[...],T2.[...],T3.[...]
from T1 
full outer join T2 on T1.[key]=T2.[key]
full outer join T3 on T1.[key]=T3.[key]
full outer join T2 on T2.[key]=T3.[key]

My question is : Is this a valid Form? And if no is there a way to do such operation?

@GordonLinoff I need to match T1 and T2 with T3 or it won't show me all records — Hamza El Alamy, Jul 09 '21 at 14:50
There is a big difference between "valid" (however you interpret it) and "useful" or "correct". But yes, you can do this . — SMor, Jul 09 '21 at 14:50
Joining `T2` twice without providing different table aliases, e.g. `T2 as T2FromT1` and `T2 as T2FromT3`, for the two references doesn't look like a good thing. Which `T2` does the select list reference? — HABO, Jul 10 '21 at 03:43

score 1 · Answer 1 · answered Jul 09 '21 at 14:50

1

It is "valid" but the full joins are not correct. The on conditions will change them to some other type of join.

Your query has other errors. But I speculate that you want:

select T1.[...], T2.[...], T3.[...]
from T1 full join
     T2 
     on T2.[key] = T1.[key] full join
     T3 join
     on T3.[key] = coalesce(T2.[key], T1.[key]);

answered Jul 09 '21 at 14:50

Gordon Linoff

1,242,037
58
646
786

I'm clearly missing something here, they look like normal FULL JOINs to me. I would think a normal commentor would be confusing CROSS JOIN and FULL JOIN but that seems really unlikely for you. Can you elaborate on what you mean? – Anthony Hancock Jul 09 '21 at 19:08
@AnthonyHancock . . . If that comment is directed to me, I'm totally baffled. Maybe you should consider asking your own question? – Gordon Linoff Jul 09 '21 at 20:29
What do you mean about the ON conditions making them not FULL JOINs? – Anthony Hancock Jul 09 '21 at 20:54
@AnthonyHancock . . . They do additional filtering due to `NULL` values. – Gordon Linoff Jul 09 '21 at 21:32

NCarlson · Answer 2 · 2021-07-09T14:59:24.693

1

It is possible to join three tables, and your example could run with some changes, but you have syntax and scoping errors in the FROM clause.

Even those aside, I don't think it will do what you intend it to do. You'll probably want to use GROUP BY

See the examples / discussion here :

Multiple FULL OUTER JOIN on multiple tables

I also used this site as a source, as its been a while since I've touched SQL, it may be helpful to you also :

https://learnsql.com/blog/how-to-join-3-tables-or-more-in-sql/

edited Jul 09 '21 at 14:59

answered Jul 09 '21 at 14:53

NCarlson

330
2
13

1

. . Actually, the sample code will not run to do syntax errors and scoping errors in the `FROM` clause (that is, even if you expand the `...` into columns as the OP intends). – Gordon Linoff Jul 09 '21 at 14:54
Ahh, I missed that. I was kinda hung up on the outer join bit/what OP was intending to do, as "The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records." why use this as opposed to just using one or more full joins. – NCarlson Jul 09 '21 at 14:58

joining three tables between each other

2 Answers2