How to filter a Table in an outer join in Query Builder? (4227287)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

How to filter a Table in an outer join in Query Builder?
Description

In Toad Data Point (TDP), If one drags tables A and B into TDP's Query Builder (QB), and joins them and select "Select All Rows form ":
Toad does show ALL ROWS from even when the Join found no matching rows in , it just shows those values as NULL:
BUT! If one adds the where statement for one of the columns in Table B:
All the NULL values are gone, meaning, it stops showing the NULL values, stops showing the Rows in when there's no maching value for it in , so, it no longer look like an Outer Join, it's an Inner Join:
Cause

Adding the WHERE statement in such way will filter the final Result Set, meaning, if one value does not match the value in the WHERE statement, it will be excluded from the result set. So, that's why the NULL values are gone, they do NOT match the value in the WHERE statement and they are excluded.
Resolution

1. - Remove the from the main Query, add a Subquery, drag into the Subquery and filter it there. Make sure you select the colum you'll use for the Outer Join further in the main Query:
2. - Create the Outer Join in the main Query:
3. - Execute again without filtering the final Result Set:
You'll now have ALL ROWS FROM , and, only the Rows from that are matching with the the WHERE statement and the OUTER JOIN: