SQL Query to find department with highest number of employees

Part 1 - How to find nth highest salary in sql 
Part 2 - SQL query to get organization hierarchy 
Part 3 - How does a recursive CTE work 
Part 4 - Delete duplicate rows in SQL 
Part 5 - SQL query to find employees hired in last n months 
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 
Part 9 - Difference between inner join and left join
This is one of the very common sql server interview question. Different JOINS in SQL Server are discussed in detail  Let's understand the difference with an example. We will be using the following tables in this demo


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.


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