Compare two query result sets

Compare matching columns in both directions when checking whether two SQL queries return the same set of rows. EXCEPT returns distinct rows from its left input that are absent on its right input.

sql

CREATE TABLE expected_result(id INTEGER, value TEXT);
CREATE TABLE actual_result(id INTEGER, value TEXT);
INSERT INTO expected_result VALUES (1, 'a'), (2, 'b');
INSERT INTO actual_result VALUES (1, 'a'), (3, 'c');

SELECT id, value FROM expected_result
EXCEPT
SELECT id, value FROM actual_result;

SELECT id, value FROM actual_result
EXCEPT
SELECT id, value FROM expected_result;

An empty result in both directions proves equality as sets, including whole-row values. It does not prove equal duplicate counts: EXCEPT removes repeated copies. If duplicates are meaningful, group each input by the compared columns, include count(*), and compare those grouped results in both directions.

Keep inputs fixed while comparing them. Different filters, column order, conversions or concurrent edits can explain a difference without a query bug. A final ORDER BY makes diagnostic output easier to read but is unnecessary for set membership.

See EXCEPT and transactions.

Compare two query result sets ​

Compare two query result sets