JOINS

Join is a method used to combine two or more tables, views or materialized views based on a common condition.

Different types of Joins

1. Cross Join/Cartesian Product
2. Natural Join
3. Inner Join
4. Outer join
           a.Left Outer Join
           b.Right outer Join
           c.Full Outer Join
5. Anti Join
6. Semi Join
7. Self Join
8. Equi Join

9.Non Equi Join

Assume that we have the following 2 tables EMPLOYEE1 and DEPARTMENT.

Here is the EMPLOYEE1 table:

SELECT * FROM EMPLOYEE1;

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI

Here is the DEPARTMENT table:

SELECT * FROM DEPARTMENT;

ID	DEPT_NAME
1	IT
2	HR
3	FINANCE

CROSS JOIN/CARTESIAN PRODUCT

Cartesian join and Cross join are one and the same thing.When we join every row of a table to every row of another table we get Cartesian join. No condition is specified here.

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT * FROM EMPLOYEE1 CROSS JOIN DEPARTMENT;

The output would be like:

ID	NAME	START_DATE	END_DATE	SALARY	CITY	ID_1	DEPT_NAME
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY	1	IT
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY	2	HR
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY	3	FINANCE
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA	1	IT
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA	2	HR
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA	3	FINANCE
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI	1	IT
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI	2	HR
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI	3	FINANCE
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI	1	IT
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI	2	HR
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI	3	FINANCE

NATURAL JOIN
Natural join compares all the common columns.

This is something similar to cross join. But eliminates duplicate values in the output.

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT * FROM EMPLOYEE1 NATURAL JOIN DEPARTMENT;

The output would be like:

ID	NAME	START_DATE	END_DATE	SALARY	CITY	DEPT_NAME
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY	IT
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA	HR
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI	FINANCE

INNER JOIN
This join returns all the rows from the both the tables where there is a match

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT EMPLOYEE1.ID, EMPLOYEE1.NAME,EMPLOYEE1.SALARY, EMPLOYEE1.CITY, DEPARTMENT.DEPT_NAME FROM EMPLOYEE1 INNER JOIN DEPARTMENT ON EMPLOYEE1.ID = DEPARTMENT.ID;

The output would be like:

ID	NAME	SALARY	CITY	DEPT_NAME
1	A	10,000	BOMBAY	IT
2	B	10,000	KOLKATA	HR
3	C	10,000	CHENNAI	FINANCE

OUTER JOIN

Outer join gives the non-matching records along with matching records.

LEFT OUTER JOIN

This will display the all matching records and also the records which are in left hand side table those that are not in right hand side table.

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT EMPLOYEE1.ID, EMPLOYEE1.NAME,EMPLOYEE1.SALARY, EMPLOYEE1.CITY, DEPARTMENT.DEPT_NAME FROM EMPLOYEE1 LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE1.ID = DEPARTMENT.ID;

The output would be like:

ID	NAME	SALARY	CITY	DEPT_NAME
1	A	10,000	BOMBAY	IT
2	B	10,000	KOLKATA	HR
3	C	10,000	CHENNAI	FINANCE
4	D	10,000	DELHI

RIGHT OUTER JOIN

This will display the all matching records and also the records which are in right hand side table those that are not in left hand side table.

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT EMPLOYEE1.ID, EMPLOYEE1.NAME,EMPLOYEE1.SALARY, EMPLOYEE1.CITY, DEPARTMENT.DEPT_NAME FROM EMPLOYEE1 RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE1.ID = DEPARTMENT.ID;

The output would be like:

ID	NAME	SALARY	CITY	DEPT_NAME
1	A	10,000	BOMBAY	IT
2	B	10,000	KOLKATA	HR
3	C	10,000	CHENNAI	FINANCE

FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT EMPLOYEE1.ID, EMPLOYEE1.NAME,EMPLOYEE1.SALARY, EMPLOYEE1.CITY, DEPARTMENT.DEPT_NAME FROM EMPLOYEE1 FULL OUTER JOIN DEPARTMENT ON EMPLOYEE1.ID = DEPARTMENT.ID;

The output would be like:

ID	NAME	SALARY	CITY	DEPT_NAME
1	A	10,000	BOMBAY	IT
2	B	10,000	KOLKATA	HR
3	C	10,000	CHENNAI	FINANCE
4	D	10,000	DELHI

ANTI JOIN 

 It returns rows from the first table where no matches are found in the second table

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT EMPLOYEE1.ID, EMPLOYEE1.NAME,EMPLOYEE1.SALARY, EMPLOYEE1.CITY FROM EMPLOYEE1 WHERE EMPLOYEE1.ID NOT IN(SELECT DEPARTMENT.ID FROM DEPARTMENT WHERE DEPARTMENT.DEPT_NAME = ‘IT’)

The output would be like:

ID	NAME	SALARY	CITY
2	B	10,000	KOLKATA
3	C	10,000	CHENNAI
4	D	10,000	DELHI

SEMI JOIN 

It returns rows from the first table where one or more matches are found in the second table.

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT EMPLOYEE1.ID, EMPLOYEE1.NAME,EMPLOYEE1.SALARY, EMPLOYEE1.CITY FROM EMPLOYEE1 WHERE EXISTS(SELECT DEPARTMENT.ID FROM DEPARTMENT WHERE DEPARTMENT.DEPT_NAME = ‘IT’)

The output would be like:

ID	NAME	SALARY	CITY
1	A	10,000	BOMBAY
2	B	10,000	KOLKATA
3	C	10,000	CHENNAI
4	D	10,000	DELHI

SELF JOIN -Here the table is joined (compared) to itself.

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT A.ID, A.NAME, B.SALQARYFROM EMPLOYEE1 A, EMPLOYEE1 B WHERE A.ID = B.ID

The output would be like:

ID	NAME	SALARY
1	A	10,000
2	B	10,000
3	C	10,000
4	D	10,000

EQUI JOIN

Equi Join is a kind of join where condition used is equal(=) sign.

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

SELECT EMPLOYEE1.ID, EMPLOYEE1.NAME,EMPLOYEE1.SALARY, EMPLOYEE1.CITY, DEPARTMENT.DEPT_NAME FROM

DSOCIQA.EMPLOYEE1 JOIN DSOCIQA.DEPARTMENT ON EMPLOYEE1.ID = DEPARTMENT.ID;

The output would be like:

ID	NAME	SALARY	CITY	DEPT_NAME
1	A	10,000	BOMBAY	IT
2	B	10,000	KOLKATA	HR
3	C	10,000	CHENNAI	FINANCE

NON EQUI JOIN

Non Equi Join uses all comparison operators except the equal (=) operator. Like >=, <=, <, >.

If T1 and T2 are two sets then cross join = T1 X T2.

EXAMPLE:

 SELECT EMPLOYEE1.ID, EMPLOYEE1.NAME,EMPLOYEE1.SALARY, EMPLOYEE1.CITY, DEPARTMENT.DEPT_NAME FROM

 DSOCIQA.EMPLOYEE1 JOIN DSOCIQA.DEPARTMENT ON EMPLOYEE1.ID > DEPARTMENT.ID;

The output would be like:

ID	NAME	SALARY	CITY	DEPT_NAME
4	D	10,000	DELHI	FINANCE
4	D	10,000	DELHI	HR
4	D	10,000	DELHI	IT
3	C	10,000	CHENNAI	HR
3	C	10,000	CHENNAI	IT
2	B	10,000	KOLKATA	IT