EXCEPT with Multiple Columns
EXCEPT with Multiple Columns is a SQL statement in the Set Operations category. EXCEPT compares ALL specified columns. Unlike NOT EXISTS, it checks the entire row combination. The syntax is SELECT col1, col2 FROM t1 EXCEPT SELECT col1, col2 FROM t2;. It returns difference rows. A typical example: SELECT product_id, warehouse_id FROM current_inventory EXCEPT SELECT product_id, warehouse_id FROM expected_inventory; -- Find discrepancies: products in wrong warehouse -- Equivalent to: SELECT product_id, warehouse_id FROM current_inventory ci WHERE NOT EXISTS ( SELECT 1 FROM expected_inventory ei WHERE ei.product_id = ci.product_id AND ei.warehouse_id = ci.warehouse_id ); -- EXCEPT is simpler and often faster for full row comparison A close relative is UNION, which combines results from two queries, removing duplicate rows. Each SELECT must have the same number of columns. A close relative is UNION ALL, which like UNION but does NOT remove duplicate rows. Faster because no dedup is needed.