Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 9 - Difference between inner join and left join

Suggested Videos:
Part 6 - Transform rows into columns in sql server
Part 7 - SQL query to find rows that contain only numerical data
Part 8 - SQL Query to find department with highest number of employees



This is one of the very common sql server interview question. Different JOINS in SQL Server are discussed in detail in Part 12 of SQL Server tutorial for beginners video series.



Let's understand the difference with an example. We will be using the following tables in this demo
Difference between inner join and left join

SQL Script to create and populate the required tables with test data
Create Table Departments
(
     DepartmentID int primary key,
     DepartmentName nvarchar(50)
)
GO

Create Table Employees
(
     EmployeeID int primary key,
     EmployeeName nvarchar(50),
     DepartmentID int foreign key references Departments(DepartmentID)
)
GO

Insert into Departments values (1, 'IT')
Insert into Departments values (2, 'HR')
Insert into Departments values (3, 'Payroll')
Insert into Departments values (4, 'Admin')
GO

Insert into Employees values (1, 'Mark', 1)
Insert into Employees values (2, 'John', 1)
Insert into Employees values (3, 'Mike', 1)
Insert into Employees values (4, 'Mary', 2)
Insert into Employees values (5, 'Stacy', 3)
Insert into Employees values (6, 'Pam', NULL)
GO

INNER JOIN returns only the matching rows between the tables involved in the JOIN. Notice that, Pam employee record which does not have a matching DepartmentId in departments table is eliminated from the result-set.
SELECT EmployeeName, DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID

LEFT JOIN returns all rows from left table including non-matching rows. Notice that, Pam employee record which does not have a matching DepartmentId in departments table is also included in the result-set. 
SELECT EmployeeName, DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID

A picture speaks thousand words. So, here is the difference between inner join and left join.
Difference between inner join and left outer join

In general there could be several questions on JOINS in a sql server interview. If we understand the basics of JOINS properly, then answering any JOINS related questions should be a cakewalk.
What is the difference between INNER JOIN and RIGHT JOIN 
INNER JOIN returns only the matching rows between the tables involved in the JOIN, where as RIGHT JOIN returns all the rows from the right table including the NON-MATCHING rows.

What is the difference between INNER JOIN and FULL JOIN 
FULL JOIN returns all the rows from both the left and right tables including the NON-MATCHING rows.

What is the Difference between INNER JOIN and JOIN
There is no difference they are exactly the same. Similarly there is also no difference between 
LEFT JOIN and LEFT OUTER JOIN
RIGHT JOIN and RIGHT OUTER JOIN
FULL JOIN and FULL OUTER JOIN

sql server interview questions and answers

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.