Anti Join - (Oracle Joins)

In Oracle SQL, an anti-join is a type of join that returns only the rows from one table that do not have a corresponding match in another table.

Syntax

The syntax for performing anti-joins in Oracle SQL is as follows:

SELECT column_name(s)
FROM table1
WHERE column_name NOT IN
   (SELECT column_name
   FROM table2);

Here, table1 is the primary table, table2 is the secondary table, and column_name is the name of the column used to join the two tables.

Example

Consider the following two tables:

Customers
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
|  3 | Carol |
|  4 | Dave  |
+----+-------+

Orders
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
|  4 |    300 |
+----+--------+

Suppose we want to find all customers who have not placed an order. We can use an anti-join as follows:

SELECT name
FROM customers
WHERE id NOT IN
    (SELECT id
     FROM orders);

The output of this query would be:

+---------+
|  name   |
+---------+
| Alice   |
| Bob     |
| Carol   |
+---------+

Explanation

In the above example, we have used an anti-join to find all customers who have not placed an order. We first select the id column from the orders table using a subquery, and then use the NOT IN operator to exclude those ids from the customers table.

Use

Anti-joins are useful when we want to find rows in one table that do not have a corresponding match in another table. This can be useful in a variety of scenarios, such as finding customers who have not placed an order, or finding products that have not been sold.

Important Points

• Anti-joins are a type of join in Oracle SQL that return only the rows from one table that do not have a corresponding match in another table.
• Anti-joins can be performed using the NOT IN operator and a subquery.
• Anti-joins are useful for finding rows in one table that do not have a corresponding match in another table.

Summary

In summary, anti-joins are a useful tool in Oracle SQL for finding rows in one table that do not have a corresponding match in another table. They use the NOT IN operator and a subquery to exclude rows from the primary table that have a match in the secondary table. Anti-joins are useful for a variety of scenarios, such as finding customers who have not placed an order, or finding products that have not been sold.