SQL
Efficiently Check for Related Data Existence with EXISTS
Discover how to use the SQL `EXISTS` operator to perform efficient existence checks for related records in a subquery, often optimizing performance over joins for simple checks.
SELECT p.product_name, p.price
FROM products p
WHERE EXISTS (
SELECT 1
FROM orders_items oi
WHERE oi.product_id = p.id
AND oi.quantity > 5
);
How it works: This query demonstrates using the `EXISTS` operator to find products that have been ordered in quantities greater than 5. `EXISTS` is particularly useful for checking if any rows satisfy a condition in a subquery without actually retrieving those rows. It returns `TRUE` as soon as the first matching row is found, making it very efficient compared to `JOIN` operations when you only need to confirm existence rather than retrieve data from the joined table.