How to use EXISTS and NOT Exists in SQL? Example Query and Tutorial

In SQL, the EXISTS and NOT EXISTS operators are used to check for the existence or non-existence of rows in a subquery. 

These operators return a boolean value (true or false) based on whether the specified condition is satisfied or not. 

 The syntax for using EXISTS and NOT EXISTS is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

SELECT column1, column2, ...
FROM table_name
WHERE NOT EXISTS (subquery);

The subquery within the parentheses is evaluated, and if it returns at least one row, the EXISTS operator evaluates to true.

On the other hand, if the subquery doesn't return any rows, the EXISTS operator evaluates to false. 

The NOT EXISTS operator does the opposite: it evaluates to true if the subquery doesn't return any rows, and false otherwise. 

Here's an example scenario to illustrate the usage of EXISTS and NOT EXISTS. Let's say we have two tables: "Customers" and "Orders". 

We want to find all customers who have placed at least one order and those who haven't placed any orders. Customers table:

+----+---------+
| ID | Name    |
+----+---------+
| 1  | John    |
| 2  | Emma    |
| 3  | William |
+----+---------+

Orders table:

+----------+------------+
| Order_ID | Customer_ID|
+----------+------------+
| 101      | 1          |
| 102      | 3          |
| 103      | 2          |
+----------+------------+

To retrieve the customers who have placed at least one order, we can use EXISTS as follows:

SELECT *
FROM Customers
WHERE EXISTS (
    SELECT *
    FROM Orders
    WHERE Orders.Customer_ID = Customers.ID
);

The result will be:

+----+---------+
| ID | Name    |
+----+---------+
| 1  | John    |
| 2  | Emma    |
| 3  | William |
+----+---------+

To retrieve the customers who haven't placed any orders, we can use NOT EXISTS:

SELECT *
FROM Customers
WHERE NOT EXISTS (
    SELECT *
    FROM Orders
    WHERE Orders.Customer_ID = Customers.ID
);

The result will be:

+----+------+
| ID | Name |
+----+------+
+----+------+

In this case, no rows are returned because there are no customers who haven't placed any orders. 

That's how you can use EXISTS and NOT EXISTS in SQL to check for the existence or non-existence of rows in a subquery.