What are Different Joins in SQL? | Real life Scenarios for SQL Joins

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

SQL Join Example
Types of Joins

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 :

Department ID Department Name
100 OBIEE
101 Oracle PLSQL

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:

Employee Name Department Name
Amit OBIEE
Rohan OBIEE
Rohit Oracle PLSQL

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:

Department Name Employee Name
OBIEE Amit
OBIEE Rohan
Oracle PLSQL Rohit
Cognos Null

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

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

Department:

Department ID Department Name
100 OBIEE
101 Oracle PLSQL
102 COGNOS

Increment Table:

Name Salary Increment
Amit 1000
Rohan 2000
Rohit 4750

Real life Scenario / SQL Join Example 3:

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:

Name Department Increment
Amit OBIEE 1000
Rohan OBIEE 2000
Rohit COGNOS 4750

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.

 

25 Replies to “What are Different Joins in SQL? | Real life Scenarios for SQL Joins”

  1. For Left outter join,
    how is the output showing NULL from a LEFT table. Isnt it supposed to show all values from Left table and then matching values from Right/Null?

  2. Hi,
    Kindly check the inner join o/p as the o/p should contain 3 rows and not 2 rows.
    The OBIEE result row should come twice, as dept-id=100 is twice present in the table.
    In short Inner Join= 3
    LEft Outer Join=3+0
    Right Outer Join= 3+1

    Do check from your end.

  3. Good concept of join tutorial, can you send me the joins and sub queries and other query based and scenario based interview questions

  4. Glad to have come across this one.
    Can you please send me more sample interview subqueries, join questions with real life samples related to join (3 or multiple joins) ?
    My id is lyn5966@gmail.com

  5. Could you please send me this tutorial pdf, and complex Queries on Joins, Sub Query with table data on my mail

Comments are closed.