SQL JOIN

The SQL Join is typically used to fetch data from two or more tables and is joined to appear as a single set of data. The SQL Join is usually used to combine columns from two or more tables by using values common to both tables.

JOIN Keyword is used in SQL queries to join two or more tables. The minimum required condition for joining a table is (n-1) where n, is the number of tables. A table can also join itself, which is known as, Self Join

 

Types of JOIN

Following are the types of JOIN that we can use in SQL:

  • Inner
  • Outer
  • Left
  • Right

 

Cross JOIN or Cartesian Product

The Cross Join is typically used to return the cartesian product of rows from the tables in Join. It returns a table that usually consists of records that combine each row from the first table with each row from the second table.

This is how its syntax looks:

SELECT column-name-list FROM table-name1 CROSS JOIN table-name2;

Example of Cross JOIN

Following is the class table,

IDNAME
1Mike
2adam
4alex

 

and the class_info table,

IDAddress
1Sydney
2Melbourne
3Perth

Cross JOIN query will be,

SELECT * FROM 
class CROSS JOIN class_info;

 

The resultset table will look like,

IDNAMEIDAddress
1Mike1Sydney
2adam1Sydney
4alex1Sydney
1Mike2Melbourne
2adam2Melbourne
4alex2Melbourne
1Mike3Perth
2adam3Perth
4alex3Perth

As you can see, this join returns the cross product of all the records present in both the tables.

 

INNER Join or EQUI Join

The INNER JOIN OR EQUI JOIN is a simple Join whereby the result is based on matched data as per the equality condition specified in the SQL query.

This is how its syntax looks:

SELECT column-name-list FROM table-name1 INNER JOIN table-name2 WHERE table-name1.column-name = table-name2.column-name;

 

Example of INNER JOIN

Consider a class table,

IDNAME
1Mike
2adam
3alex
4anu

and the class_info table,

IDAddress
1Sydney
2Melbourne
3Perth

Inner JOIN query will be,

SELECT * from class INNER JOIN class_info where class.id = class_info.id;

 

The resultset table will look like,

IDNAMEIDAddress
1Mike1Sydney
2adam2Melbourne
3alex3Perth

 

Natural JOIN

The Natural Join is typically used based on a column having the same name and same datatype present in both the tables to be joined.

This is how its syntax looks:

SELECT * FROM table-name1 NATURAL JOIN table-name2;

 

Example of Natural JOIN

Here is the class table,

IDNAME
1abhi
2adam
3alex
4anu

and the class_info table,

IDAddress
1DELHI
2MUMBAI
3CHENNAI

Natural join query will be,

SELECT * from class NATURAL JOIN class_info; 

 

The resultset table will look like,

IDNAMEAddress
1abhiDELHI
2adamMUMBAI
3alexCHENNAI

From the example above, you can see that both the tables being joined have an ID column (i.e same name and same data type). Therefore, the records in which the value of ID matches in the two tables will be the result of the Natural Join of the tables.

 

OUTER JOIN

The Outer Join is typically used for both matched and unmatched data. It is subdivided into three parts, namely:

  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join

LEFT Outer Join

Firstly, the Left outer join is typically used to return a result-set table with the matched data obtained from two tables, and then the remaining rows of the left table and null from the right table's columns.

This is how its syntax looks:

SELECT column-name-list FROM table-name1 LEFT OUTER JOIN table-name2 ON table-name1.column-name = table-name2.column-name;

 

In other to specify a condition, you can use the ON keyword with Outer Join.

Left outer Join Syntax for Oracle is written below;

SELECT column-name-list FROM table-name1, table-name2 on table-name1.column-name = table-name2.column-name(+);

Example of Left Outer Join

Here is the class table,

IDNAME
1abhi
2adam
3alex
4anu
5ashish

and the class_info table,

IDAddress
1DELHI
2MUMBAI
3CHENNAI
7NOIDA
8PANIPAT

 

Left Outer Join query will look like this;

SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id = class_info.id);

 

The resultset table will look like,

IDNAMEIDAddress
1abhi1DELHI
2adam2MUMBAI
3alex3CHENNAI
4anunullnull
5ashishnullnull

 

RIGHT Outer Join

The right outer join is typically used to return a result-set table for the matched data from the joined two tables, then the remaining rows of the right table, and null for the remaining left table's columns.

This is how its syntax looks:

SELECT column-name-list FROM table-name1 RIGHT OUTER JOIN table-name2 ON table-name1.column-name = table-name2.column-name;

Right outer Join Syntax for Oracle can be written as;

SELECT column-name-list FROM table-name1, table-name2 ON table-name1.column-name(+) = table-name2.column-name;

 

Example of Right Outer Join

Once again the class table,

IDNAME
1abhi
2adam
3alex
4anu
5ashish

and the class_info table,

IDAddress
1DELHI
2MUMBAI
3CHENNAI
7NOIDA
8PANIPAT

Right Outer Join query will be,

SELECT * FROM class RIGHT OUTER JOIN class_info ON (class.id = class_info.id);

The resultant table will look like,

IDNAMEIDAddress
1abhi1DELHI
2adam2MUMBAI
3alex3CHENNAI
nullnull7NOIDA
nullnull8PANIPAT

 

Full Outer Join

The full outer join is basically used to return a result-set table with the matched data of two tables, and then the remaining rows of both left tables, then the right table.

This is how its syntax looks:

SELECT column-name-list FROM table-name1 FULL OUTER JOIN table-name2 ON table-name1.column-name = table-name2.column-name;

 

Example of Full outer join is,

The class table,

IDNAME
1abhi
2adam
3alex
4anu
5ashish

and the class_info table,

IDAddress
1DELHI
2MUMBAI
3CHENNAI
7NOIDA
8PANIPAT

Full Outer Join query will be like,

SELECT * FROM class FULL OUTER JOIN class_info ON (class.id = class_info.id);

 

The resultset table will look like,

IDNAMEIDAddress
1abhi1DELHI
2adam2MUMBAI
3alex3CHENNAI
4anunullnull
5ashishnullnull
nullnull7NOIDA
nullnull8PANIPAT