Oracle Semi Join FAQs with Examples

1. What is a Semi Join in Oracle?

A Semi Join returns rows from the first (outer) table only when at least one matching row exists in the second table.

Unlike an inner join, a Semi Join does not return columns from the second table, and each qualifying row from the first table is returned only once.

Oracle does not have a SEMI JOIN SQL keyword. The optimizer may internally transform certain queries into a semi join.

The most common way to write a Semi Join is with:

• EXISTS (recommended)
• IN

2. Why do we use a Semi Join?

A Semi Join is used to:

• Check whether related records exist.
• Filter rows based on matching records.
• Avoid duplicate rows caused by regular joins.
• Improve readability for existence checks.
• Retrieve only data from the first table.

3. What is the syntax of a Semi Join?

Using EXISTS (recommended):

SELECT column_list
FROM table1 t1
WHERE EXISTS
(
  SELECT 1
  FROM table2 t2
  WHERE t1.column_name = t2.column_name
);

4. How does a Semi Join work?

Suppose we have:

CUSTOMERS

CUSTOMER_ID   CUSTOMER_NAME
1             Alice
2             Bob
3             Charlie

ORDERS

ORDER_ID   CUSTOMER_ID
100        1
101        1
102        2

Query

SELECT customer_name
FROM customers c
WHERE EXISTS
(
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Output

Customer
Alice
Bob

Although Alice has two orders, she appears only once.

5. Why is EXISTS preferred for a Semi Join?

EXISTS:

• Stops searching once the first matching row is found.
• Clearly expresses an existence check.
• Is commonly optimized by Oracle into a semi join.
• Avoids duplicate rows from the second table.

Example:

SELECT employee_name
FROM employees e
WHERE EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

6. Can a Semi Join be written using IN?

Yes.

SELECT employee_name
FROM employees
WHERE department_id IN
(
  SELECT department_id
  FROM departments
);

This is logically similar to:

SELECT employee_name
FROM employees e
WHERE EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

7. What is the difference between EXISTS and IN?

EXISTS	IN
Correlated subquery	Usually non-correlated subquery
Stops after first match	Compares against the complete result set
Often preferred for correlated existence checks	Often convenient for simple membership tests

Both can produce the same results in many cases, but the optimizer may choose different execution plans.

8. Can a Semi Join use aliases?

Yes.

SELECT e.employee_name
FROM employees e
WHERE EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

9. Can a Semi Join use a WHERE clause?

Yes.

SELECT e.employee_name
FROM employees e
WHERE e.salary > 5000
AND EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

10. Can a Semi Join use multiple conditions?

Yes.

SELECT *
FROM orders o
WHERE EXISTS
(
  SELECT 1
  FROM shipments s
  WHERE s.order_id = o.order_id
  AND s.customer_id = o.customer_id
);

11. Can a Semi Join use multiple tables?

Yes.

SELECT c.customer_name
FROM customers c
WHERE EXISTS
(
  SELECT 1
  FROM orders o
  JOIN payments p
  ON o.order_id = p.order_id
  WHERE o.customer_id = c.customer_id
);

12. Does a Semi Join return columns from the second table?

No. A Semi Join returns only columns from the outer query.

SELECT customer_name
FROM customers c
WHERE EXISTS
(
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

The query returns only CUSTOMER_NAME.

13. Can a Semi Join return duplicate rows?

Normally, no, provided the driving (outer) table has unique rows.

Even if multiple matching rows exist in the second table, the outer row is returned only once.

Example:

Alice has two orders.

Result:

Customer
Alice

Not:

Customer
Alice
Alice

14. What are common uses of a Semi Join?

• Customers with orders.
• Employees assigned to departments.
• Students enrolled in courses.
• Products that have sales.
• Suppliers with purchase orders.
• Orders that have shipments.

15. Real-Time Example – Customers Who Placed Orders

CUSTOMERS

CUSTOMER_ID   CUSTOMER_NAME
1             Alice
2             Bob
3             Charlie

ORDERS

ORDER_ID   CUSTOMER_ID
100        1
101        2

Query

SELECT customer_name
FROM customers c
WHERE EXISTS
(
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Output

Customer
Alice
Bob

Charlie is excluded because no order exists.

16. What are the advantages of a Semi Join?

• Efficient for checking existence.
• Avoids duplicate rows from joins.
• Returns only required data.
• Easy to understand.
• Commonly optimized by Oracle.

17. What are the disadvantages of a Semi Join?

• Returns data only from the outer table.
• Cannot directly display columns from the related table.
• Complex correlated subqueries can be harder to maintain.
• Performance depends on indexes and optimizer choices.

18. Common Errors with Semi Joins

Error	Cause
ORA-00904	Invalid column name
ORA-00942	Table or view does not exist
ORA-00933	SQL command not properly ended
ORA-00936	Missing expression

19. Semi Join vs Inner Join

Semi Join	Inner Join
Returns only columns from the first table	Returns columns from both tables
Each qualifying outer row appears once	May return multiple rows if multiple matches exist
Used for existence checks	Used to retrieve related data

Example:

Semi Join

SELECT customer_name
FROM customers c
WHERE EXISTS
(
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Inner Join

SELECT c.customer_name,
       o.order_id
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;

20. Semi Join vs Anti Join

Semi Join	Anti Join
Returns rows that have a match	Returns rows that do not have a match
Uses EXISTS	Uses NOT EXISTS

Semi Join

WHERE EXISTS (...)

Anti Join

WHERE NOT EXISTS (...)

21. Semi Join vs DISTINCT

Sometimes an inner join with DISTINCT produces the same result as a semi join.

SELECT DISTINCT c.customer_name
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;

However, using EXISTS is often clearer because it directly expresses an existence check.

22. How does Oracle execute a Semi Join?

Although SQL has no SEMI JOIN keyword, Oracle's optimizer may choose execution plans such as:

• HASH JOIN SEMI
• MERGE JOIN SEMI
• NESTED LOOPS SEMI

The execution method depends on factors such as indexes, statistics, and table sizes.

23. Interview Scenario

Question: Display customers who have placed at least one order.

Answer: Use EXISTS.

SELECT customer_name
FROM customers c
WHERE EXISTS
(
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

24. Which method is best for implementing a Semi Join?

General recommendation:

1. EXISTS – Best choice for correlated existence checks.
2. IN – Good for simple membership tests.
3. Inner Join + DISTINCT – Use only when you also need data from the joined table or for specific query requirements.

---

Semi Join is a clean and efficient way to test for existence, and in Oracle it is most commonly expressed with EXISTS.