SQL Full outer join

The full outer join returns a result table with the matched data of two table then remaining rows of both lefttable and then the right table.

Full Outer Join Syntax is,

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,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Full Outer Join query will be like,

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

The result table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
4	anu	null	null
5	ashish	null	null
null	null	7	NOIDA
null	null	8	PANIPAT

---

SQL Right Outer Join

The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table's columns.

Right Outer Join Syntax is,

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 is,

select column-name-list
from table-name1, 
table-name2
on table-name1.column-name(+) = table-name2.column-name;

---

Example of Right Outer Join

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Right Outer Join query will be,

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

The result table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
null	null	7	NOIDA
null	null	8	PANIPAT

SQL Left Outer Join

The left outer join returns a result table with the matched data of two tables then remaining rows of the lefttable and null for the right table's column.

Left Outer Join syntax is,

SELECT column-name-list
from table-name1 
LEFT OUTER JOIN 
table-name2
on table-name1.column-name = table-name2.column-name;

Left outer Join Syntax for Oracle is,

select column-name-list
from table-name1, 
table-name2
on table-name1.column-name = table-name2.column-name(+);

---

Example of Left Outer Join

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Left Outer Join query will be,

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

The result table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
4	anu	null	null
5	ashish	null	null

SQL Natural JOIN

Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.

Natural Join Syntax is,

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

---

Example of Natural JOIN

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

Natural join query will be,

SELECT * from class NATURAL JOIN class_info; 

The result table will look like,

ID	NAME	Address
1	abhi	DELHI
2	adam	MUMBAI
3	alex	CHENNAI

In the above example, both the tables being joined have ID column(same name and same datatype), hence the records for which value of ID matches in both the tables will be the result of Natural Join of these two tables.

SQL INNER Join or EQUI Join

This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the query.

Inner Join Syntax is,

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

---

Example of Inner JOIN

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

Inner JOIN query will be,

SELECT * from class, class_info where class.id = class_info.id;

The result table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI

SQL Cross JOIN or Cartesian Product

This type of JOIN returns the cartesian product of rows of from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table.

Cross JOIN Syntax is,

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

Example of Cross JOIN

The class table,

ID
NAME
1
abhi
2
adam
4
alex


The class_info table,

ID
Address
1
DELHI
2
MUMBAI
3
CHENNAI


Cross JOIN query will be,
SELECT *
 from class,
 cross JOIN class_info;


The result table will look like,

ID
NAME
ID
Address
1
abhi
1
DELHI
2
adam
1
DELHI
4
alex
1
DELHI
1
abhi
2
MUMBAI
2
adam
2
MUMBAI
4
alex
2
MUMBAI
1
abhi
3
CHENNAI
2
adam
3
CHENNAI
4
alex
3
CHENNAI

SQL Types Of Joins

The following are the types of JOIN that we can use in SQL.

• Inner
• Outer
• Left
• Right