Joins in SQL

Introduction

SQL joins are used to relate information in different table or say joins are used to combine two or more tables.

Types of Join
 Types of joins are

1. Self Join
2. Inner Join
3. Outer Join
   Outer Join are three types:
   
   (a) Right Outer Join
   (b) Left Outer Join
   (c) Full Outer Join
    
4. Cross Join
   Cross  Join are two types:
   
   (a) Implicit cross join
   (b) Explicit cross join

Now, I explain each and every separately.

1- Self Join

A "self-join" joins a table itself. Basically "self-join" are rare, they are some time useful for retrieving data that can't retrieved any other way.

Syntax

SELECT a.columnName, b.columnName.... FROM table1 a, table1 b where a.commonField=b.common_field;

Example

This example describes you how to play with SELF-JOIN.Look "empdtl" table design

Now I am inserting some data in that table, After inserting some values table data look like

Now, I want to get details of those "employee's", who are manager. Then I simply write a query for it.

SELECT distinct e1.empid, e1.empname FROM empdtl e1, empdtl e2 where e1.empid=e2.managerid;Output

2- Inner Join
The Join condition indicates how the two table should be compared. The "inner join" is a join in which the values in the columns being joined are compare using a common operator. You can simple say "inner join" selects all rows from both table as long as there is a match between the columns in both tables.
Syntax

SELECT columnsName or selectlist FROM table1 INNER JOIN table2 ON table1.columnName=table2.columnName

OR

SELECT columnsName or selectlist FROM table1  JOIN table2 ON table1.columnName=table2.columnName

Here I shown "empdtl" and "dept" table data as a image for using the INNER JOIN

Query For Inner Join 

Here I want to get the "deptname" from  the "dept" table, "id" and "name" from "empdtl" table, on the behalf of "id"  of the "empdtl" table, Let's see how to do it:

SELECT empdtl.id,empdtl.name,dept.deptname from empdtl join dept on empdtl.id=dept.deptid;

 Output

 

3- Outer Join

The "outer join" are three types I explain each and every separately:(a) Right Outer Join
 

Right Outer join returns all rows from the right table, with the matching row in the left table, the result is NULL in the left side when there is no match.Data of both "empdtl" and "dept" table 

Example

SELECT empdtl.id,empdtl.name, dept.deptname from empdtl RIGHT OUTER JOIN dept on empdtl.id=dept.deptid;Output

(b) Left Outer Join

The "left outer join" is used to returning the all rows from the left table, with the matching row in the right table and the NULL is appear in the right side when there is no match.
Data of both "empdtl" and "dept" table 

Example

SELECT empdtl.id,empdtl.name, dept.deptname from empdtl LEFT OUTER JOIN dept on empdtl.id=dept.deptid;Output

(c) Full Outer Join

The "full outer join" is used to returns all woes from the left and right table or you can say full outer join combines the result of both left and right joins.

Example

SELECT empdtl.id,empdtl.name, dept.deptname from empdtl FULL OUTER JOIN dept on empdtl.id=dept.deptid;Output

4- Cross Join

A "cross join" produces a result set that indicates each row from the first table joined with each row of the second table and this result set is known as the Cartesian Product of the table and the "cross join" have two types.

(a) Explicit cross join

To use an "explicit cross join", write the "cross join" keywords after the first table name and before the second table name

Syntax

SELECT columnsName or selectList FROM table1 CROSS JOIN table2

Example

SELECT id,name,deptname from empdtl CROSS JOIN deptOutput

(b) Implicit  cross join

To use an "implicit  cross join", you do not need to write the "cross join" keywords after the first table name and before the second table name, you just simply write comma (,) symbol after the first table name and before the second table name.

Syntax

SELECT columnsName or selectList FROM table1, table2

Example

SELECT id,name,deptname from empdtl,deptOutput