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:

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

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:

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:

Difference Between Anti-Join and Semi-Join

Conclusion

Anti-join filters out rows based on the absence of matches in another table, while semi-join filters rows based on the existence of related records but only returns columns from the first table. Understanding the differences between these two operations is crucial for effective data analysis and reporting in relational databases.