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,

and the class_info table,

Cross JOIN query will be,

SELECT * FROM 
class CROSS JOIN class_info;

The resultset table will look like,

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,

and the class_info table,

Inner JOIN query will be,

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

The resultset table will look like,

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,

and the class_info table,

Natural join query will be,

SELECT * from class NATURAL JOIN class_info; 

The resultset table will look like,

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,

and the class_info table,

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,

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,

and the class_info table,

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,

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,

and the class_info table,

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,