D8LooPFocus modeCODE WORKSPACE
Orders with no line items
Return o.order_id, o.status, and o.total_amount for orders that have no matching rows in order_items. Order by order_id.
LEFT JOIN order_items AS oi, then WHERE oi.order_id IS NULL keeps orders with no items.
Sample input
ordersAll 12 orders (order_id, status, total_amount shown).
| order_id | status | total_amount |
|---|---|---|
| 1001 | paid | 80 |
| 1003 | pending | 40 |
| 1006 | refunded | 60 |
| 1009 | pending | 30 |
| 1011 | paid | 110 |
| 1012 | shipped | 75 |
order_itemsDistinct order_ids that have items: 1001, 1002, 1004, 1005, 1007, 1008, 1010.
| order_id | sku |
|---|---|
| 1001 | SKU-A |
| 1002 | SKU-C |
| 1004 | SKU-A |
| 1010 | SKU-G |
Expected output
Expected outputOrders whose order_id never appears in order_items — the referential gaps.
| order_id | status | total_amount |
|---|---|---|
| 1003 | pending | 40 |
| 1006 | refunded | 60 |
| 1009 | pending | 30 |
| 1011 | paid | 110 |
| 1012 | shipped | 75 |
Constraints
LEFT JOIN orders to order_items on order_id, then keep only rows where order_items.order_id IS NULL. This is a referential gap check.
Expected skills
The anti-join pattern as a referential-integrity check: finding parent rows with no child rows.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.