SQL Join Tutorial | SQL Join Example | SQL Join 3 tables | Inner Join | Outer Join | SQL Join with Multiple Table

  • Joins in SQL[Inner Join|Outer Join]:

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.

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.

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 100 550000
    3 Rohit 101 430000

    There is another table called as Depatment 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 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 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
Null Cognos

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

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 :

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

 

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 analysing 2 tables we got to know that there is join between Employee and Department with Department_ID.

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.name=i.name;

Output:

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

 

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

HOME

About admin 136 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development

10 Trackbacks / Pingbacks

  1. SQL set Operators |Union|Union all|Real Life Scenarios|Difference between Union and Union all
  2. SQL Joins | Postgres Joins | MSSQL Joins | Full Outer Join |Cartesian Join
  3. SQL Indexes |Performance Tuning |Normal Index | Unique Index| Bitmap Index | Global Index | Local Index
  4. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  5. Sed Command in Unix with examples | Stream Editor in Unix | Pipe Command with Examples | Head and Tail command with examples
  6. Filter Commands in Unix | Sort command with Example | Uniq Command with Example | Date Command with Example
  7. Chmod command in unix | Unix File Permissions | chmod with examples | Chwn command | Chgrp command | UNMASK
  8. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database
  9. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions
  10. Interview Questions for HCL | SQL Interview Questions for HCL | Interview Questions Asked in HCL | HCL Interview Questions | 20 Most Important SQL Interview Questions for HCL

Leave a Reply

Your email address will not be published.


*