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 ANTI JOIN

In Oracle, ANTI JOIN returns rows from the first table where no matches are found in the second table. It is opposite of a semi-join. Anti-join is used to make the queries run faster. It is a very powerful SQL construct Oracle offers for faster queries. Anti joins are written using the NOT EXISTS or NOT IN constructs.

Oracle ANTI JOIN Syntax

To retrive rows from the first table where no matches are found in the second table, use the following syntax:

Syntax

SELECT col1, col2,....
FROM tablename1 A
WHERE NOT 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 ANTI JOIN example

The following Oracle, retrive matching column values of the associated tables using ANTI JOIN:

Example

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

Note:

In the above example, we are retriving matching column values of the associated tables using ANTI JOIN.