IT2EDU Empowering Education Through Technology
In SQL, an anti join is a type of JOIN that returns only the rows from one table that do not have matching rows in the other table. This means that an anti join is useful when you want to find records that are present in one table but not in another.
To illustrate an anti join in SQL, consider the following two tables: “customers” and “orders”. The “customers” table contains information about customers, including their name, address, and ID. The “orders” table contains information about orders, including the customer ID, order number, and order date.
Suppose you want to find all the customers who have not placed an order in the last month. You can do this using an anti join as follows:
SELECT *
FROM customers
LEFT JOIN orders
ON customers.ID = orders.customer_ID
WHERE orders.order_date < '2023-04-21' OR orders.order_date IS NULL;
In this example, we are performing a LEFT JOIN between the “customers” table and the “orders” table on the “customer_ID” column. The WHERE clause includes a condition that specifies that the order date must be less than ‘2023-03-23’ (i.e., the date one month ago) or null. This will return all the customers who either have not placed an order at all or have not placed an order in the last month.
Alternatively, you can use the NOT EXISTS operator to achieve the same result:
SELECT *
FROM customers
WHERE NOT EXISTS (
SELECT 1
FROM orders
WHERE customers.ID = orders.customer_ID
AND orders.order_date >= '2023-04-21'
);
In this example, we are using the NOT EXISTS operator to return all the customers for whom there is no corresponding order in the “orders” table with an order date greater than or equal to ‘2023-04-21’.
Both of these queries return the same result: a list of customers who have not placed an order in the last month. This is an example of how you can use an anti join to find records that are present in one table but not in another, making it a valuable tool for data analysis in SQL.
