Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle SEMI JOIN

In Oracle, SEMI JOIN returns one copy of each row in first table for which at least one match is found. SEMI JOIN are written using the EXISTS construct. It provides an efficient method of performing a WHERE EXISTS sub-query.

Oracle SEMI JOIN Syntax

To returns one copy of each row in first table for which at least one match is found, use the following syntax:

Syntax

SELECT col1, col2,....
FROM tablename1 A
WHERE EXISTS (SELECT 1 FROM tablename2 B WHERE A.column_name = B.column_name); 

Parameters:

tablename1: The table name from which you want to fetch records.

col1: The column or field name that will be return.

Oracle SEMI JOIN example

The following Oracle, returns one copy of each row in first table for which at least one match is found using SEMI JOIN:

Example

SELECT A.firstName, A.lastName, A.address
FROM tblCustomerLoan A
WHERE EXISTS (SELECT 1 FROM tblCustomer B WHERE B.loanID = A.ID); 

Note:

In the above example, it returns one copy of each row in first table for which at least one match is found using SEMI JOIN.