Some other database platforms have an 'EXCEPTION JOIN' construct for excluding rows in one table that have no matching row in another table — what's the Oracle/IFS equivalent for a Quick Report or SQL query?

Q: Some other database platforms have an 'EXCEPTION JOIN' construct for excluding rows in one table that have no matching row in another table — what's the Oracle/IFS equivalent for a Quick Report or SQL query?

Use a NOT EXISTS correlated subquery instead — for example, to exclude Customer Order Lines whose customer appears in a custom exclusion table: SELECT order_no, part_no, base_sale_unit_price FROM CustomerOrderLine col WHERE col.contract = 'W' AND NOT EXISTS (SELECT 1 FROM Exclude_Customers_Cfv ec WHERE ec.cf$_customer_id = col.customer_id);. This is the standard Oracle SQL way to express 'give me rows from Table A that have no matching row in Table B,' achieving the same result an EXCEPTION JOIN would on another platform.

Answer

Use a NOT EXISTS correlated subquery instead — for example, to exclude Customer Order Lines whose customer appears in a custom exclusion table: SELECT order_no, part_no, base_sale_unit_price FROM CustomerOrderLine col WHERE col.contract = 'W' AND NOT EXISTS (SELECT 1 FROM Exclude_Customers_Cfv ec WHERE ec.cf$_customer_id = col.customer_id);. This is the standard Oracle SQL way to express 'give me rows from Table A that have no matching row in Table B,' achieving the same result an EXCEPTION JOIN would on another platform.

← Browse Reporting and analytics questions