[Solved] Fetching Unmatching/Rejected rows

by


It appears that you want to do something like

select *
  from Tab1
  LEFT OUTER join Tab2
    on Tab1.col1=Tab2.col1 and
       Tab1.col2=Tab2.col3 and
       Tab1.col4=Tab2.col5 AND
       Tab2.col3=<value>
  LEFT OUTER join Tab3
    ON (TAB3.SOMETHING = TAB1.SOMETHING OR
        TAB3.SOMETHING_ELSE = TAB2.SOMETHING_ELSE) AND
       Tab3.col2=<value> and
  WHERE Tab1.col3=<value> and
        Tab1.col4=<value> AND
        TAB2.PRIMARY_KEY IS NULL AND
        TAB3.PRIMARY_KEY IS NULL;

Note the use of outer joins. A LEFT OUTER JOIN means that data must be present in the left table (e.g. TAB1 in the join between TAB1 and TAB2), but optionally may not exist in the right table. Contrast this with

  1. an INNER JOIN where matching data must exist in both tables for the result to be included in the result set,
  2. a RIGHT OUTER JOIN, where data must exist in the right-most table but optionally may not exist in the left table, and
  3. a FULL OUTER JOIN where data must exist in one of the two tables but may not have matching data in the other table. (A full outer join can be thought of as a left and a right outer join simultaneously).

The WHERE clause checks for TAB2.PRIMARY_KEY and TAB3.PRIMARY_KEY being NULL. By definition a primary key must be non-NULL, so the fact the the PK column is NULL in the joined data means that no matching row was found in the joined table.

Your example query suffers from the problem that there’s no condition given for joining TAB3 to either TAB1 or TAB2. Although you could do this, it would mean that every row from the join of TAB1 and TAB2 would be joined to every row from TAB3 where TAB3.COL2 = . So if the join of TAB1 and TAB2 emitted 1000 rows, and TAB3 contained 1000 rows, your result table would end up with 1000 * 1000 = 1,000,000 rows – probably not what was intended. I added a join condition just to keep things reasonable.

1

solved Fetching Unmatching/Rejected rows