Semi Join

A Semi Join is a type of join that returns rows from the outer table where there is at least one matching row in the inner table, but only the columns from the outer table are included in the result. The inner table is only used to check for the existence of matching rows but is not included in the final result set.

In simpler terms, a Semi Join is a way to filter the outer table by matching rows in the inner table, but only the rows from the outer table are returned.

Key Points of Semi Join:

1.     Purpose of Semi Join:

• A Semi Join is used when you want to filter records from the outer table based on the existence of matching rows in the inner table but do not require any data from the inner table to be included in the result.
• It is similar to an INNER JOIN, but only the columns from the outer table are returned.
• A Semi Join is often used to reduce the size of the result set by excluding rows in the outer table that do not have a corresponding row in the inner table.

2.     How Semi Join Works:

• Unlike a full INNER JOIN, where columns from both the outer and inner tables are included in the final result, a Semi Join only returns rows from the outer table that have at least one matching row in the inner table.
• The inner table does not contribute any columns to the result set; it is merely used to filter the outer table.

3.     Key Operators for Semi Joins:

• EXISTS: The most common way to implement a Semi Join. It checks whether a subquery returns any rows for a given row in the outer table.
• IN: Another way to implement a Semi Join, though it is less efficient than EXISTS when working with large datasets.

Implementing a Semi Join in Oracle:

1. Using EXISTS:

The EXISTS clause is a powerful way to implement a Semi Join. It checks for the existence of rows in the inner table that match the outer table’s row. If a matching row is found, the outer row is returned.

Syntax:

SELECT outer_table.column_list

FROM outer_table

WHERE EXISTS (

    SELECT 1

    FROM inner_table

    WHERE outer_table.column = inner_table.column

);

Example:

Consider two tables:

• customers: A list of customers.
• orders: A list of customer orders.

If you want to find customers who have placed at least one order, you would write a Semi Join using EXISTS like this:

SELECT c.customer_id, c.customer_name

FROM customers c

WHERE EXISTS (

    SELECT 1

    FROM orders o

    WHERE c.customer_id = o.customer_id

);

This query will return customers who have at least one matching record in the orders table. Only the columns from the customers table are returned, even though the orders table is used for filtering.

2. Using IN:

The IN operator can also be used to implement a Semi Join. It is generally less efficient than EXISTS, especially when the subquery returns a large number of rows. However, in cases where the inner query returns a small number of distinct values, it can work effectively.

Syntax:

SELECT outer_table.column_list

FROM outer_table

WHERE outer_table.column IN (

    SELECT inner_table.column

    FROM inner_table

);

Example:

Using the same customers and orders tables, you could write the query using IN as follows:

SELECT c.customer_id, c.customer_name

FROM customers c

WHERE c.customer_id IN (

    SELECT o.customer_id

    FROM orders o

);

This query will return the same result as the EXISTS version: customers who have placed at least one order. Like the previous query, it only returns columns from the customers table, not from orders.

3. Performance Considerations:

• EXISTS is typically more efficient than IN, especially when the inner table has a large number of rows. This is because EXISTS stops processing as soon as it finds a match, whereas IN must process all the rows in the subquery before making a comparison.
• IN may not perform well with large subqueries, particularly if the subquery contains many rows or NULL values. In contrast, EXISTS handles larger datasets more efficiently.
• Using EXISTS ensures that the query will return at most one match per row in the outer table, as it only checks for the existence of matching rows rather than returning multiple rows.

When to Use Semi Joins:

1.     Finding Rows with Matching Entries: Use a Semi Join when you need to find rows in the outer table that have matching rows in the inner table but do not need to return any data from the inner table. For example, customers who have placed orders or products that are listed in a catalog.

2.     Improving Query Performance: In some cases, using a Semi Join can be more efficient than using an INNER JOIN if you only need to know if a match exists but do not require any columns from the inner table.

3.     Avoiding Redundant Data: When you want to return rows from the outer table only once, even if there are multiple matching rows in the inner table. For instance, you may have multiple orders per customer, but you only need to list each customer once.

Example Scenarios:

1. Find Customers Who Have Placed Orders:

Using the EXISTS approach, you can find customers who have made at least one purchase:

SELECT c.customer_id, c.customer_name

FROM customers c

WHERE EXISTS (

    SELECT 1

    FROM orders o

    WHERE c.customer_id = o.customer_id

);

This will return the list of customers who have placed at least one order, without including any order details.

2. Find Products That Have Been Sold:

Suppose you have two tables, products and sales, and you want to find products that have been sold at least once.

SELECT p.product_id, p.product_name

FROM products p

WHERE EXISTS (

    SELECT 1

    FROM sales s

    WHERE p.product_id = s.product_id

);

This query returns the list of products that have been sold, but it does not return any sales details. It filters out products that have no corresponding sale record.

3. Find Students Who Have Completed a Course:

Consider two tables, students and course_completions. To find students who have completed at least one course, you can use the EXISTS approach.

SELECT s.student_id, s.student_name

FROM students s

WHERE EXISTS (

    SELECT 1

    FROM course_completions c

    WHERE s.student_id = c.student_id

);

This query will return a list of students who have completed at least one course.

Conclusion:

A Semi Join in Oracle is a powerful way to filter rows from the outer table based on the existence of matching rows in the inner table. It is particularly useful when you want to:

• Return only the rows from the outer table where there is a match in the inner table.
• Avoid including any columns from the inner table in the final result set.

You can implement a Semi Join using EXISTS or IN, with EXISTS generally being the more efficient choice for larger datasets. When used appropriately, Semi Joins can help improve query performance and simplify complex filtering operations.