Full outer join acts like inner join with multiple conditions on the two tables

Question

I am trying to have a full outer join between two tables Table1 and Table2 on ID with a query like the following in Teradata. The problem is it acts like inner join.

SELECT  *
    FROM Table1 AS a   
    FULL OUTER JOIN Table2 AS b
    ON a.ID = b.ID
    WHERE a.country in ('US','FR')         
        AND a.create_date = '2021-01-01'
        AND b.country IN ('US','DE','BE')
        AND b.create_date = '2021-01-01';

What I want is something like this:

SELECT * FROM 
(
    SELECT * FROM Table1 as a 
    WHERE a.country in ('US','FR')
        AND a.create_date = '2021-01-01'
) as ax
FULL OUTER JOIN
(
    SELECT * FROM Table2 as b 
    WHERE b.country IN ('US','DE','BE')     
        AND b.create_date = '2021-01-01'
) as bx
ON ax.ID=bx.ID;

I feel like the second query is not best practice, maybe inefficient and/or hard to read in complicated cases. How can I modify the first query to get the desired output?

I know that this is a fundamental problem and probably there are many other ways to do it (e.g. with USING, HAVING etc) but could not find a basic explanation. Would appreciate a comprehensive answer on alternative solutions as a guide for future reference.

EDIT The difference in my question to Left Join With Where Clause is that I require a condition in both tables. I cannot figure out where to put the second WHERE condition.

LEFT/RIGHT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left/right table rows extended by NULLs. FULL gives INNER JOIN ON rows UNION ALL unmatched left & right table rows extended by NULLs. Always know what INNER JOIN ON is part of your OUTER JOIN ON. After an OUTER JOIN ON a WHERE, INNER JOIN or HAVING needing some column(s) of the right/left/2 [sic] table(s) to be not NULL removes rows with introduced NULLs, leaving only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". Needing a non-NULL right/left column "turns FULL into RIGHT/LEFT". You have that. — philipxy, May 02 '22 at 09:16
Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) — philipxy, May 02 '22 at 09:18
"I want is something like this" does not constitute a [mre]. PS When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) Your overall goal is a different question, and how can you approach it if your error was from not understanding the language, and why should you think you're stuck when you haven't corrected your understanding of the language first? (Rhetorical.) PS "I feel" is not helpful. — philipxy, May 02 '22 at 09:22
I'm not sure that you can avoid your workaround query. If you shift the `WHERE` restrictions to the `ON` clause, then you might get unwanted rows in the result set. — Tim Biegeleisen, May 02 '22 at 09:23
After you pin down a problem via a [mre]: Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] PS [Why is asking a question on "best practice" a bad thing?](https://meta.stackexchange.com/q/142353/266284) — philipxy, May 02 '22 at 09:27
@philipxy The previous question you refer to is helpful, but does not really answer my question. In mine, I have a `WHERE` condition on both cases. I could not figure out where to put the second 'WHERE' condition and hence my question (I edited my question). Thanks for the other explanations though. — volkan g, May 02 '22 at 10:11
I just said, debugging & going forward are 2 different question. Also, the duplicate addresses removing null extended rows on the right from left join & by symmetry left table null-extend rows from right join, and full join has rows extended both ways. And I even left a comment that explains full join specifically. Also the full join case is almost certainly a duplicate, I just don't see a link in my list of frequent basic duplicates. But did you act on my comment re searching? (Rhetorical.) — philipxy, May 02 '22 at 10:16
[Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/a/55111083/3404097) — philipxy, May 02 '22 at 10:20
It's a simple rule. Logically speaking, WHERE filters rows _after_ the join. If you want filtering to occur _prior_ to the join, move the predicates (for either or both tables) to the ON clause. You can even do that with INNER joins. An alternative (though sometimes less desirable) is to modify predicates in the WHERE clause to not filter out the NULL values. — Fred, May 02 '22 at 16:37
Another way to think about it: ON specifies both _which_ rows from a table are eligible to participate in the JOIN and _how_ those rows get matched against the other table. WHERE filters the final result. — Fred, May 02 '22 at 19:50

score 1 · Answer 1 · answered May 03 '22 at 16:01

The short answer: Both sets of predicates belong in the ON clause.

SELECT  *
    FROM Table1 AS a   
    FULL OUTER JOIN Table2 AS b
    ON a.ID = b.ID
        AND a.country in ('US','FR')         
        AND a.create_date = '2021-01-01'
        AND b.country IN ('US','DE','BE')
        AND b.create_date = '2021-01-01';

The ON clause both limits the rows that are eligible to participate in the join (pre-join filtering) and specifies how to match rows (join criteria). The WHERE clause filters results (after the join).

A generally less-desirable alternative would be to modify the predicates so as not to filter out the non-matching rows, e.g. assuming ID is NOT NULL in both tables

SELECT  *
    FROM Table1 AS a   
    FULL OUTER JOIN Table2 AS b
    ON a.ID = b.ID
    WHERE (a.country in ('US','FR')         
        AND a.create_date = '2021-01-01'
        OR a.ID IS NULL)
        AND (b.country IN ('US','DE','BE')
        AND b.create_date = '2021-01-01'
        OR b.ID IS NULL);

Logically the ON and WHERE work the same way for INNER JOIN but in that case the net result is the same (and many databases including Teradata will generate the same query plan for INNER JOIN regardless of where you put the filter predicates).

Full outer join acts like inner join with multiple conditions on the two tables

1 Answers1