Different Joins in SQL :

Joins in SQL are nothing but combining the 2 or more tables and fetch the columns from the tables. Joins in SQL are very useful in day to day real life Scenarios whether it is reporting or it is in stand alone applications or web applications. In Previous article we have given the brief information about Equi join and Non Equi join. I have explained the SQL Join Example in this article with Real life Scenarios.

What you will find in this article?

1.Different joins in SQL

2.Types of Different joins in SQL

3.Examples of Different joins in SQL

This important article gives you the information about Inner join and Outer Join in SQL. Both inner and outer joins are very useful to achieve the functionality. Kindly make sure that the tables are related to each other before applying join.

Different Joins in SQL : Inner Join:

When 2 tables are connected such that it should retrieve only the matching records in both tables. Inner join select only the matching records between 2 tables. You can use Equal to(=) operator or Inner join keyword to apply inner join. This join is most widely used joins in real life applications,reporting,webapps,android apps.

Inner Joins is nothing but fetching the common records from two or more tables.

Syntax :

Type 1:

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1,table2 t2

where t1.col=t2.col;

Type 2:

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1 inner join table2 t2

on t1.col=t2.col;

• Real life Scenario/SQL Join Example 1:

• Question: What is the query to display the allocated departments with its department id?

  Consider there are 2 tables. Employee table which has following columns:

  Employee_num	Employee_name	Department ID	Salary
  1	Amit	100	680000
  2	Rohan	102	550000
  3	Rohit	101	430000

  There is another table called as Department which has following structure:

  Department ID	Department Name
  100	OBIEE
  101	Oracle PLSQL
  102	COGNOS

• SQL Query:

Using Type 1:

Select a.Department_ID,a.Department_Name from

Employee b,Department a

where a.Department_ID=b.Department_ID;

Using Type 2:

Select a.Department_ID,a.Department_Name from

Employee b inner join Department a

on a.Department_ID=b.Department_ID;

Output :

Hope You will get Idea about Inner join.

Outer join :

When user fetches a data from left table and right table as well as its common records then this kind of join is known as Outer join. This is also one of the important join used in most of the real life scenarios. Outer join displays the common matching records between two tables and uncommon records from left or right table. The records not matching are considered as null.

There are two types of Outer joins:

1.Left Outer Join

2.Right Outer Join

1.Left Outer join:

When user wants all the records from Left table (First table) and only equal or matching records from second table then Left outer join is useful.The unmatched records are considered as null records.

Syntax :

Type 1:Left Outer Join Syntax with +

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1,table2 t2

where t1.col=t2.col(+);

Type 2:With Keyword

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1 Left Outer join table2 t2

on t1.col=t2.col;

• Real life Scenario / SQL Join Example 2:

• Question: What is the query to fetch employees associated with department with all department names?

  Consider there are 2 tables. Employee table(Considered as left table) which has following columns:

  Employee_num	Employee_name	Department ID	Salary
  1	Amit	100	680000
  2	Rohan	100	550000
  3	Rohit	101	430000

  There is another table called as Department(Considered as right table) which has following structure:

  Department ID	Department Name
  100	OBIEE
  101	Oracle PLSQL
  102	COGNOS

• SQL Query:

Using Type 1:

Select a.Employee_name,b.Department_Name from

Employee a,Department b

where a.Department_ID=b.Department_ID(+);

Using Type 2:

Select a.Employee_name,b.Department_Name from

Employee a left outer join Department b

on.Department_ID=b.Department_ID;

Output:

In specified scenario none of the Employee is associated with cognos.So You will not get any values for COGNOS.It will fetch all values of left table and common values of left and right table.

1.Right Outer join:

When user wants all the records from Right table (Second table) and only equal or matching records from First or left table then Right outer join is useful. The unmatched records are considered as null records.

Syntax :

Type 1:Right Outer Join Syntax with +

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1,table2 t2

where t1.col(+)=t2.col;

Type 2:With Keyword

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1 Right Outer join table2 t2

on t1.col=t2.col;

• Real life Scenario:

• Question: What is the query to fetch all departments with its associated employees?

  Consider there are 2 tables.Employee table(Considered as left table) which has following columns:

  Employee_num	Employee_name	Department ID	Salary
  1	Amit	100	680000
  2	Rohan	100	550000
  3	Rohit	101	430000

  There is another table called as Depatment(Considered as right table) which has following structure:

  Department ID	Department Name
  100	OBIEE
  101	Oracle PLSQL
  102	COGNOS

• SQL Query:

Using Type 1:

Select b.Department_Name,a.Employee_name from

Employee a,Department b

where a.Department_ID(+)=b.Department_ID;

Using Type 2:

Select b.Department_Name,a.Employee_name from

Employee a right outer join Department b

on.Department_ID=b.Department_ID;

Output:

In specified scenario none of the Employee is associated with cognos.So you will see null is displayed.

Hope You like this Article about joins. To get PDF notes of joins click below link :

Hope you will like this Article.Please comment below if you want any other information or notes.

• Joins Between Multiple Tables / SQL Join 3 tables / SQL Join Example with multiple tables :

Consider there are following 3 tables which have following structure:

Table 1:Employee Table

Department:

Increment Table:

Joins in SQL  : Industry example :

I  need to display departmentwise,EmployeeName with its salary increment.

Step 1:

First Step is to fetch departmentwise employee name.After analyzing 2 tables we got to know that there is join between Employee and Department with Departmentwide.

Select e.Employee-name,d.Department-name from Employee e , Department d

where e.Department_no=d.Department_no;

Step 2:

Second Step is Join between Department and Increment table.Name is the column.

Select e.Employee-name,d.Department-name from Employee e , Department d,Increment I where e.Department_no=d.Department_no and d.Employee-name=i.name;

Output:

Document to refer :

>>>> Click Here to get notes on Inner Join and outer join in PDF<<<<<<<<<<

Video to refer :

Hope you will get idea of joining multiple tables. If you want SQL Join Example PDF copy kindly comment it in comment section.