In MySQL, The EXISTS keyword is used in a subquery to test whether the result of the subquery is not empty.
The EXISTS condition is typically used with the WHERE clause to check for the existence of rows that satisfy certain conditions.
SELECT column1, column2, ... FROM table_name WHERE EXISTS (subquery);
column1, column2, ...: The columns we want to retrieve from the table.
table_name: The name of the table from which we want to retrieve data.
EXISTS: A condition that returns true if the specified subquery returns one or more rows.
This will retrieve all the customers from the "customers" table who have orders in the orders table. we can use the "EXISTS" condition for this:
SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id );
Note: In this example, the outer query selects all columns from the customers table, and the EXISTS condition checks whether there is at least one row in the orders table where the "customer_id" matches the "customer_id" in the outer query.
SELECT * FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );
We can also use "NOT EXISTS" to check for the absence of rows that satisfy certain conditions:
Note: This query retrieves all customers who do not have orders in the orders table.
The EXISTS condition is particularly useful when we want to filter rows based on the existence or non-existence of related records in another table.
Keep in mind that the subquery inside the EXISTS clause should return one or more rows for the condition to be true.