JOIN IN ORACLE - Online Tech Support
  Home
 

The first Oracle Antijoin on this tutorial page will return only rows from the master table that’s values couldn’t find or did not have match in the subquery. The syntax is:

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

This Oracle join example will show only query “A” rows that do not exist in query “B“:

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a
WHERE a.id NOT IN ( SELECT rownum + 6 AS Id
                      FROM DUAL
                   CONNECT BY rownum < 11);

online tech support computer help computer technician computer problems oracle database oracle join oracle join outer join oracle outer join semijoin oracle semijoin in oracle anijoin oracle in anijoin retirement planning retirement

This Oracle join will be useful to use in cases where you need to exclude more than one NOT NULL value and when the ID column is indexed it will make the query much faster. Keep in mind that NULL (empty) values in the subquery select part will cause all the query returning no rows. The another way to write antijoin queries is to use NOT EXIST keywords. You could use this query to exclude rows using more complicated conditions in the slave query or in the situations when there is no exact column value to exclude. Also the NOT EXIST condition would work with the possible NULL values in the joining columns. The NOT EXIST antijoin syntax is following:

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

The next example will return no lines because the Oracle Dual table has a row by default. This Dual table is used as slave query or subquery in Antijoin example:

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a
WHERE NOT EXISTS ( SELECT *                      
                     FROM DUAL );

online tech support computer help computer technician computer problems oracle database oracle join oracle join outer join oracle outer join semijoin oracle semijoin in oracle anijoin oracle in anijoin retirement planning retirement

The second join group is Oracle Semijoin and it will return all rows from the master table that has a match in the slave table. This Semijoin query is using the EXISTS keyword, but without NOT as it was on the last example. The Semijoin syntax is:

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

This type of Oracle joins can be used when the direct join condition does not exists or it could contain NULL values or there are more than 1 key column whom value should match with the master table. The following join example will return all lines in the master table since the Dual table has always a row by default.

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a
WHERE EXISTS ( SELECT *                      
                 FROM DUAL );

online tech support computer help computer technician computer problems oracle database oracle join oracle join outer join oracle outer join semijoin oracle semijoin in oracle anijoin oracle in anijoin retirement planning retirement


See Also:
Home Previous Page