Referential integrity audit

Return check_name and failing_rows for two checks: 'orders_without_items' and 'orphan_line_items'. Order by check_name.

Write each direction as a LEFT JOIN with an IS NULL filter, count the rows, then UNION ALL them.

Sample input

ordersFive orders (1003, 1006, 1009, 1011, 1012) have no rows in order_items.

order_id	status
1003	pending
1006	refunded
1009	pending
1011	paid
1012	shipped

order_itemsEvery order_items.order_id exists in orders, so there are no orphan line items.

order_id	sku
1001	SKU-A
1002	SKU-C
1004	SKU-A
1005	SKU-F
1007	SKU-B

Expected output

Expected outputTwo referential checks as counts; the orphan check passes with 0.

check_name	failing_rows
orders_without_items	5
orphan_line_items	0

Constraints

Use a LEFT JOIN + IS NULL anti-join for each direction, COUNT the failing rows, and UNION ALL the two checks. A clean direction must return 0, not no rows.

Expected skills

Anti-join referential checks and reporting gaps as counts.

SQL

AI evaluation

Run the SQL query to inspect preview rows.