Semi Join - (Oracle Joins)

A semi join in Oracle joins two tables and returns only the matched rows from the first table, omitting any duplicate rows. It is similar to an inner join, but the result set contains only columns from the first table.

Syntax

The syntax for a semi join in Oracle is as follows:

SELECT column1, column2, ...
FROM table1
WHERE EXISTS (SELECT column1 FROM table2 WHERE condition);

Here, table1 is the first table, table2 is the second table, and condition is a conditional statement that determines the rows to be returned from table2.

Example

Suppose we have two tables, employees and departments, with the following data:

employees table

employee_id | employee_name | department_id
------------+--------------+--------------
1           | John         | 1
2           | Jane         | 2
3           | Bob          | 1
4           | Mary         | 3
5           | Mark         | 1

departments table

department_id | department_name
--------------+----------------
1             | Sales
2             | Marketing
3             | Finance

We can perform a semi join on these tables to return the names of all employees in the Sales department, like this:

SELECT employee_name
FROM employees
WHERE EXISTS (SELECT department_id FROM departments
              WHERE department_name = 'Sales'
              AND departments.department_id = employees.department_id);

Output

employee_name
-------------
John
Bob
Mark

Explanation

In the above example, we first select the employee_name column from the employees table. We then filter the results by using the WHERE EXISTS clause to check if there are any rows in the departments table with a department_name of 'Sales' that match the department_id in the employees table. Since there are three employees in the Sales department, we get all their names as a result.

Use

Semi joins are useful for finding matching rows in one table based on the existence of a row in another table that meets certain criteria. They are often used in conjunction with inner joins and outer joins to narrow down the result set to a specific subset of data.

Important Points

• A semi join in Oracle joins two tables and returns only the matched rows from the first table.
• The result set contains only columns from the first table.
• Semi joins are useful for finding matching rows in one table based on the existence of a row in another table that meets certain criteria.
• Semi joins are often used in conjunction with inner joins and outer joins to narrow down the result set to a specific subset of data.

Summary

In summary, a semi join in Oracle is a useful tool for finding matching rows in one table based on the existence of a row in another table that meets certain criteria. It allows you to narrow down the result set to a specific subset of data, and is often used in conjunction with inner joins and outer joins in complex queries.