Difference Between Anti-Join and Semi-Join

Last Updated : 13 Nov, 2023

In the context of SQL, Anti-join, and semi-join are two essential operations in relational databases used for querying and manipulating data. These operations focus on comparing data from two related tables, but they serve distinct purposes. In this article let us discuss these two operations in detail along with some examples.

What is SQL Join?

Join is an operation performed on the relations or tables that combine rows from two or more relations or tables based on the related columns between them.

Different kinds of joins can be applied to the relations which include:

Semi-join

Semi-join is a type of join that is applied to relations to join them based on the related columns. When semi-join is applied, it returns the rows from one table for which there are matching records in another related table.

Characteristics:

A Semi-join returns rows from the left table for which there are corresponding matching rows in the right table.
Unlike regular joins which include the matching rows from both tables, a semi-join only includes columns from the left table in the result.

Syntax:

using `EXISTS`:

SELECT column1, column2, …FROM table1

WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column = table2.column);

using `IN`:

SELECT column1, column2, …FROM table1WHERE column IN (SELECT column FROM table2);

Example:

Let us consider two relations namely “Customers” and “Orders“. The Customers table contains the list of customer’s details along with their ID and name. The Orders table contains the details of the orders placed by the customers along with the customer ID, Order Id, and the name of the order placed by the customer.

Query

Creating Customers Table:

CREATE TABLE Customers (
    Customer_ID VARCHAR(20) PRIMARY KEY,
    Customer_Name VARCHAR(20)
);

Inserting into Customers Table:

INSERT INTO Customers (Customer_ID, Customer_Name) VALUES ('01', 'Alice');
INSERT INTO Customers (Customer_ID, Customer_Name) VALUES ('02', 'Bob');
INSERT INTO Customers (Customer_ID, Customer_Name) VALUES ('03', 'Charlie');
INSERT INTO Customers (Customer_ID, Customer_Name) VALUES ('04', 'David');

Customers table:

Customer_ID	Customer_Name
01	Alice
02	Bob
03	Charlie
04	David

Query

Creating Orders Table:

CREATE TABLE Orders (
    Customer_ID VARCHAR(20),
    Order_ID VARCHAR(20) PRIMARY KEY,
    Order_Name VARCHAR(20)
);

Inserting into Orders Table:

INSERT INTO Orders (Customer_ID, Order_ID, Order_Name) VALUES ('02', '101', 'Stationery');
INSERT INTO Orders (Customer_ID, Order_ID, Order_Name) VALUES ('01', '102', 'Pens');
INSERT INTO Orders (Customer_ID, Order_ID, Order_Name) VALUES ('04', '103', 'Books');

Orders Table

Customer_ID	Order_ID	Order_Name
02	101	Stationery
01	102	Books
04	103	Pens

Query for Semi-Join:

SELECT Customers.Customer_ID, Customers.Customer_Name
FROM Customers
WHERE EXISTS (
  SELECT 1
  FROM Orders
  WHERE Customers.Customer_ID = Orders.Customer_ID
);

Output:

Customer_ID	Customer_Name
01	Alice
02	Bob
04	David

Anti-Join

Anti-join or Anti-semi-join is a type of join that is applied to relations to join them based on the related columns. When anti-join is applied, it returns the rows from one table for which there are no matching records in another related table.

Characteristics:

It is Exactly the opposite to semi-join.
An Anti-join returns rows from the left table for which there are no corresponding matching rows in the right table

Syntax:

SELECT column1, column2,…FROM table1

LEFT JOIN table2 ON table1.column_name = table2.column_name

WHERE table2.column_name IS NULL;

Example:

Let us consider the above example only which has Customers and Orders tables.

Query for Anti-Join

SELECT Customers.*
FROM Customers
LEFT JOIN Orders 
ON Customers.Customer_ID = Orders.Customer_ID
WHERE Orders.Customer_ID IS NULL;

Output:

Customer_ID	Customer_Name
03	Charlie