Back
Syntax
Study
Editor
Mode:
HTML
CSS
JavaScript
PHP
Reset
Run »
HTML / CSS / JS
-- LATERAL JOIN — top 3 orders per user SELECT u.name, o.id AS order_id, o.total, o.created_at FROM users u CROSS JOIN LATERAL ( SELECT id, total, created_at FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3 ) o; -- LATERAL with LEFT JOIN — include users with no orders SELECT u.name, COALESCE(o.total, 0) AS latest_order_total FROM users u LEFT JOIN LATERAL ( SELECT total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1 ) o ON TRUE; -- UNION ALL — combine two result sets (keeps duplicates) SELECT id, name, 'user' AS source FROM users UNION ALL SELECT id, name, 'admin' AS source FROM admins; -- EXCEPT — products never ordered SELECT id, name FROM products EXCEPT SELECT DISTINCT p.id, p.name FROM products p JOIN order_items oi ON oi.product_id = p.id; -- INTERSECT — users in both tables SELECT email FROM newsletter_subscribers INTERSECT SELECT email FROM users WHERE is_active = TRUE;
Result
Open