Different Types Of Join

Different Types Of Join

Join is a question that is used to blend series from two or more table, views, or materialized views. It retrieves information from several tables and helps to produce a new table.

Join Conditions

There may be at least one join situation either in the FROM clause or in the WHERE clause for becoming a member of two tables. It analyzes two columns from different tables and brings together couple of series, each containing one row from each desk, for which join situation applies.

When accessing information from tables to make concerns and views, you can use join circumstances, or connects, to specify connections between tables, usually in accordance with the main and international important factors. For example, you can use join circumstances to evaluate principles in two tables that have typical areas and come back only those information where those areas have the same principles. You can set join circumstances for concerns and views in the Question and Perspective Developers and when using the Visible FoxPro terminology.

Factors of Join Conditions

The following record explains some elements of join circumstances and requirements that you can set between tables or in concerns and views:

  1. Join type between tables or between table areas in a query or view

  2. Table areas in the join

  3. Comparison situation providers between areas, such as Equal (=), Higher Than (>), and Between

  4. Sequence of connects for more than two tables

Join Types

You can specify join types between tables or fields to increase or decrease the outcomes of your search. For example, selecting a full join increases the outcomes to include the information that coordinate and do not coordinate the join rule. They are as follows:

  • Inner Joins (Simple Join)

  • Outer Joins

    • Left Outer Join (Left Join)

    • Right Outer Join (Right Join)

    • Full Outer Join (Full Join)

  • Equijoins

  • Self Joins

  • Cross Joins (Cartesian Products)

  • Antijoins

  • Semijoins

Inner Joins

Inner Join is the easiest and the most common type of join. Simple join is the other name for Inner join. When the join case is met it returns all the rows from various tables.


  1. SELECT columns  

  2. FROM table01   

  3. INNER JOIN table02  

  4. ON table01.column = table02.column;   

Outer Joins

An outer join is the identical to equijoin but it gets also the non-matched rows from the table. It is categorized in Left Outer Join, Right Outer Join and Full Outer Join by Oracle 9i ANSI/ISO 1999 conventional.

Left Outer Join

Left Outer Join gives back all series from the left (first) table specified in the ON situation and only those series from the right (second) table where the join situation is met.


  1. SELECT columns  

  2. FROM table1  

  3. LEFT [OUTERJOIN table2  

  4. ON table1.column = table2.column;   

Right Outer Join

The Right Outer Join give back all the sequence from the right-hand table specified in the ON scenario and only those sequence from the other table where the Join scenario is met.


  1. SELECT columns  

  2. FROM table1  

  3. RIGHT [OUTERJOIN table2  

  4. ON table1.column = table2.column;   

Full Outer Join

The Full Outer Join gives back all series from the left-hand table and right side table. It places NULL where the join condition is not met.


  1. SELECT columns  

  2. FROM table1  

  3. FULL [OUTERJOIN table2  

  4. ON table1.column = table2.column;   

Equi Join

Oracle Equi join gives back the matching column values of the associated tables. It uses a comparison operator in the WHERE clause to relate equal rights.


  1. SELECT column_list   

  2. FROM table1, table2….  

  3. WHERE table1.column_name =  

  4. table2.column_name;  

Self Join

Self join is a particular kind of join. In Self Be Join, a table is joined with itself (Unary relationship). A self join basically identifies that each series of a table is put together with itself and every other row of the desk.


  1. SELECT a.column_name, b.column_name…   

  2. FROM table1 a, table1 b   

  3. WHERE a.common_filed = b.common_field;   


The CROSS JOIN identifies that all series from first table join with all of the series of second table. If there are “x” series in table1 and “y” series in table2 then the cross join outcome set have x*y series. It normally happens when no related join content are specified.

In easy terms you can say that if two platforms in a join question have no join situation, then the Oracle gives their Cartesian item.


  1. SELECT *   

  2. FROM table1   

  3. CROSS JOIN table2;  


  1. SELECT * FROM table1, table2  

Anti Join

Anti-join is used to create the concerns run quicker. It is a very highly effective SQL build Oracle provides for quicker concerns.

Anti-join between two platforms gives back series from the first table where no suits are discovered in the second table. It is reverse of a semi-join. An anti-join gives one duplicate of each row in the first table for which no coordinate is discovered.

Semi Join

A semi-join gives back series from the first feedback if there is at least one related row in the second feedback. An anti-join gives series from the first feedback if there are no related series in the second feedback. You use the EXCEPT and INTERSECT providers to carry out semi-joins and anti-joins. These providers allow you to evaluate and contrast the outcomes of two or more SELECT claims and come back unique principles. The EXCEPT operator gives any unique principles from the query on the remaining part of the EXCEPT operator that are not also came back by the question on the right part. INTERSECT profits any unique principles that are came back by both the query on the left and right ends of the INTERSECT operator. Outcome places that are in comparison using EXCEPT or INTERSECT must all have the same structure.



You can join the oracle dba training to get the oracle dba jobs and thus make your career in this field.


Don't be shellfish...Digg thisBuffer this pageEmail this to someoneShare on FacebookShare on Google+Pin on PinterestShare on StumbleUponShare on LinkedInTweet about this on TwitterPrint this pageShare on RedditShare on Tumblr

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>