How To Write Antijoin And Semijoin In Oracle

How To Write Antijoin And Semijoin In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online tutorial is based on examples to make it easier to follow. The Oracle antijoin is a join type that returns only rows from the master table that’s value couldn’t find in the sub-query. The join syntax is:

SELECT <columns> 
  FROM <table1> 
 WHERE <columns> NOT IN (SELECT <columns> FROM <table2>);

The Oracle antijoin example is showing only table my_a rows that does not exists in table my_b:

WITH my_a AS (
 SELECT rownum AS a_Id
   FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
   FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a
WHERE a_id NOT IN (SELECT b_id FROM my_b);

dba oracle dba database management system oracle performance tuning oracle join oracle join in oracle outer join oracle outer join in oracle web design courses learn web design oracle semijoin oracle semijoin in oracle antijoin oracle antijoin in oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This Oracle join type is useful in cases where you need to exclude more than one value. To make the query faster use an index on ID column. Another way to write Antijoin in Oracle query is to use NOT EXIST keywords. You can use this query type in situations where the query condition in slave query part is very complicated or there isn’t exact value to compare with main query. The syntax is following:

SELECT <columns> 
  FROM <table1> 
 WHERE NOT EXISTS (SELECT <columns> FROM <table2>);

The following example will return rows from table “my_a” that did not have a match in table “my_b“:

WITH my_a AS (
 SELECT rownum AS a_Id
   FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
   FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a
WHERE NOT EXISTS (SELECT * FROM my_b WHERE a_id = b_id );

dba oracle dba database management system oracle performance tuning oracle join oracle join in oracle outer join oracle outer join in oracle web design courses learn web design oracle semijoin oracle semijoin in oracle antijoin oracle antijoin in oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

The Oracle Semijoin will return all rows from a master table that has match in a slave table. In this Semijoin in Oracle we are going to use EXISTS keyword, but without NOT – as reverse than we did in the last example. The syntax is:

SELECT <columns> 
  FROM <table1> 
 WHERE EXISTS (SELECT <columns> FROM <table2>);

This type of Oracle join can be used when the direct value is missing or there are more than 1 key column whom value should match with the master table. The example below will return all rows in the master table “my_a” that has a match in table “my_b“.

WITH my_a AS (
 SELECT rownum AS a_Id
   FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
   FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a
WHERE EXISTS (SELECT * FROM my_b WHERE a_id = b_id);

dba oracle dba database management system oracle performance tuning oracle join oracle join in oracle outer join oracle outer join in oracle web design courses learn web design oracle semijoin oracle semijoin in oracle antijoin oracle antijoin in oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

See Also:

Previous Page

Leave a Reply

Your email address will not be published. Required fields are marked *

*