eeyo
asked on
SQL OUTER JOIN Syntax with filters for both tables
What is the correct syntax for the first "WHERE statement" in this Full Join statement? (for MSSQL)
SELECT * FROM TableA WHERE col1 IS not NULL
FULL OUTER JOIN (Select * from TableB WHERE col2 > 2) T2
ON TableA.orderID = T2.orderID
You could also use this, with is more like your original query (but I would not do it that way):
select *
from (SELECT * FROM TableA WHERE col1 IS not NULL) TableA
full join (Select * from TableB WHERE col2 > 2) T2
on TableA.orderID = T2.orderID
Usually it is better to have all conditions in the main query:select *
from TableA
full join TableB
on TableA.orderID = TableB.OrderID
where TableA.col1 is not null
and TableB.col2 > 2
ASKER
select *
from TableA
full join TableB
on TableA.orderID = TableB.OrderID
where TableA.col1 is not null
and TableB.col2 > 2
For this version, if a row doesn't exist in TableA, but it exists in TableB, will "TableA.col1 is not null" filter out the row in TableB (because it is a full join)? If the row exists only in TableB, it needs to be returned with "col1" being null. Sorry, I can't test it right now because I won't have access to the DB for a while.
That sounds about right. If you don't want those rows, why not just do a left join instead of a full join ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Putting the condition in the ON section should work.
Though I ask myself why you exclude col1 - if there is a row in TableA with matching orderID, but col1 is null, that row will not "match", and TableA cols all be null.In this report, we need to know when there isn't a matching row in TableA when it exists in TableB (and col1 should show up as Null). It's a somewhat strange report but the above SQL statement will help.
Open in new window
Select columns from tab1 full outer join tab2 on tab1.col1 = tab2.col2 where <conditions> is the general format.