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 :
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 |
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:
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.
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?
Hi Rahul,
Thanks for correction.It was small printing mistake.
Regards,
Amit
Thanks for the tutorial can you please mail this pdf
Sure snehal i sent you tutorial questions!!!
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.
Yes ..You are absolutely right..It was typo and i have corrected that mistake…Thanks for lovely suggestions!!!
Nice tutorial can send me the joins and sub queries materials and scenario based interview questions
I have sent you the SQL interview questions on your mail id.
Good concept of join tutorial, can you send me the joins and sub queries and other query based and scenario based interview questions
Sure Rajeeb…I will send you interview questions..
Hi please share the subqueries, CTE , join queries questions also SQL analytic funtion real life examples. My id is pallaviilla12@gmail.com
Your explanations are amazing
Sure pallavi..
Kindly check your inbox.
thanks for the tutorial …can you send me interview questions related to joins
Sure…
Kindly check your inbox for the same.
PLEASE SHARE THE PDF. REALLY USEFUL TUTORIAL
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
Thanks for good words Lyn!
I sent you interview questions on your mail id.
regards,
Amit S
Hi , could you please share where I can get some more scenario based questions
Sure Himali..I have shared you questions..
very lucid tutorial. kindly share more scenario based questions
Thanks for your Kind words Surbhi I have shared you scenarios on your mail id.
Hello, Thanks for the tutorial could you send the pdf on mail.
Sure Hitendra . Kindly check your inbox
Could you please send me this tutorial pdf, and complex Queries on Joins, Sub Query with table data on my mail
Sure Anurag. Kindly check your mail for all tutorials