MySQL - Exists Queries

The EXISTS statement is used to check whether a subquery returns any rows. The EXISTS statement returns a Boolean value (true or false) based on whether the subquery returns any rows.

Suppose you have two tables named "orders" and "order_details". The "orders" table contains information about customer orders, including the order ID and customer ID. The "order_details" table contains information about the items in each order, including the order ID and product ID. If you want to find all orders that contain a specific product, you can use EXISTS statement in the following way:

SELECT order_id, customer_id

FROM orders

WHERE EXISTS (

  SELECT *

  FROM order_details

  WHERE orders.order_id = order_details.order_id

  AND product_id = 'P123'

);

In this example, we're selecting the order ID and customer ID from the "orders" table. We're using EXISTS statement to check if there is at least one record in the "order_details" table where the order ID matches the order ID in the "orders" table and the product ID is 'P123'. If the subquery returns any rows, the EXISTS statement will evaluate to true, and the corresponding row from the "orders" table will be included in the result set. If the subquery returns no rows, the EXISTS statement will evaluate to false, and the corresponding row from the "orders" table will be excluded from the result set.

Another example of using EXISTS statement:

Suppose you have a table named "employees" that contains information about employees, including their ID, name, and department. You also have another table named "departments" that contains information about the departments, including their ID, name, and budget. If you want to find all employees who work in departments with a budget greater than ₹500,000, you can use EXISTS statement in the following way:

SELECT *

FROM employees

WHERE EXISTS (

  SELECT *

  FROM departments

  WHERE employees.department = departments.name

  AND budget > 500000

);

In this example, we're selecting all columns from the "employees" table. We're using EXISTS statement to check if there is at least one record in the "departments" table where the department name matches the department of the employee and the budget is greater than 500,000. If the subquery returns any rows, the EXISTS statement will evaluate to true, and the corresponding row from the "employees" table will be included in the result set. If the subquery returns no rows, the EXISTS statement will evaluate to false, and the corresponding row from the "employees" table will be excluded from the result set.