SQL JOIN

SQL JOIN

SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data. It is used for combining column from two or more tables by using values common to both tables.

JOIN Keyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is (n-1) where n, is number of tables. A table can also join to 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

·         Equi

Cross JOIN or Cartesian Product

This type of JOIN returns the cartesian product of rows 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

Select column-name

From table-name1  CROSS JOIN Table-name 2

Example of Cross JOIN

Following is the std table,& std info

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

ID	NAME
1	Sachin
2	Vishal
3	Sangamesh













  Cross JOIN query will be,

   Select * from std cross join std;

   The result set table will look like

ID	NAME	ID	Address
1	Sachin	1	DELHI
2	Vishal	1	DELHI
3	Sangamesh	1	DELHI
1	Sachin	2	MUMBAI
2	Vishal	2	MUMBAI
3	Sangamesh	2	MUMBAI
1	Sachin	3	CHENNAI
2	Vishal	3	CHENNAI
3	Sangamesh	3	CHENNAI

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 SQL query.

Inner Join Syntax is,

SELECT    column-name-list

FROM    table-name1 INNER JOIN table-name2

WHERE   table column-n table column-name;

Example of INNER JOIN

Consider a Std table,

ID	NAME
1	Rahul
2	Virat
3	Rohit
4	Dhoni

 

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

and the std info table,

Inner JOIN query will be,

SELECT * from std INNER JOIN stdinfo where std.id = stdinfo.id;

The resultset table will look like,

ID	NAME	ID	Address
1	Rahul	1	DELHI
2	Virat	2	MUMBAI
3	Rohit	3	CHENNAI

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.

The syntax for Natural Join is,

SELECT * FROM

table-name1 NATURAL JOIN table-name2;

Example of Natural JOIN

Here is the std table,

and the std info table,

Natural join query will be,

SELECT * from std NATURAL JOIN stdinfo;

The resultset table will look like,

ID	NAME	Address
1	Rahul	DELHI
2	virat	MUMBAI
3	Rohit	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.

OUTER JOIN

Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,

1.    Left Outer Join

2.    Right Outer Join

3.    Full Outer Join

LEFT Outer Join

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

Syntax for Left Outer Join is,

SELECT column-name-list FROM

table-name1 LEFT OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

To specify a condition, we use the ON keyword with Outer Join.

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

Here is the Bkec table,

ID		NAME
1		abhi
2		abay
3		laxmi
4		anu
ID		Address
1		DELHI
2		MUMBAI
3		CHENNAI
7		NOIDA
8		PANIPAT

and the bkecinfo table,

Left Outer Join query will be,

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

The resultset table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	abay	2	MUMBAI
3	laxmi	3	CHENNAI
4	anu	null	null
5	ashish	null	null

---

RIGHT Outer Join

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

Syntax for Right Outer Join 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

Once again the bkec table,

and the bkecinfo table,

Right Outer Join query will be,

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

The resultant table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	abay	2	MUMBAI
3	laxmi	3	CHENNAI
null	null	7	NOIDA
null	null	8	PANIPAT

---

Full Outer Join

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

Syntax of Full Outer Join 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 bkec table,

and the bkecinfo table,

Full Outer Join query will be like,

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

The resultset table will look like,

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