SQL Server Query – Real time example for right join

Click Here for complete List.

In the Last post we have seen difference between INNER JOIN and LEFT JOIN and about the RIGHT JOIN. Now, let us see the real-time example of where RIGHT JOIN is used.

In the interview, we can answer the question to RIGHT JOIN by explaining that it will return all the rows from both the tables and the non-matching rows from right table used in the join condition.

If we have to answer the question of real-time example of RIGHT JOIN I can think of these 2 classic examples.

  1. Take example of Departments and Employees in an organization and can prepare the report to list the employees count by each department, and the Departments where there are no employees.
  2. Another example is Products and the Order details for an e-commerce firm. If the management want to see the reports which shows orders by Products and the Products where there are no orders yet.

Let’s see the above examples in action with the below script. All you have to do is copy paste the below script and follow the comments I have given against each query.

DEPARTMENTS AND EMPLOYEES EXAMPLE

/*
    STEP - 1. CREATE DEPARTMENT TABLE FOR THIS EXAMPLE.
*/
--First declare a table and populate the data for this example.
create table #Department (
    DeptId int primary key,
    DepartmentName varchar(100)
)

/*
    STEP - 2. INSERT SOME SAMPLE TEST DATA IN TO DEPARTMENT TABLE.
*/
--Populating some sample data in to the above table.
insert into #Department(DeptId,DepartmentName)
    values  (1,'HR'),
            (2,'Accounts'),
            (3,'IT'),
            (4,'PayRoll'),
            (5,'Finance')

/*
    STEP - 1. CREATE EMPLOYEE TABLE FOR THIS EXAMPLE.
*/
--First declare a table and populate the data for this example.
create table #Employees (
    EmpId int primary key,
    EmployeeName varchar(100),
    DeptId int foreign key references #Department(DeptId)
)

/*
    STEP - 2. INSERT SOME SAMPLE TEST DATA IN TO EMPLOYEE TABLE.
*/
--Populating some sample data in to the above table.
insert into #Employees(EmpId,EmployeeName,DeptId)
    values  (1,'siva',1),
            (2,'kumar',1),
            (3,'komal',2),
            (4,'jashank',2),
            (5,'Arjun',2),
            (6,'Ram',2),
            (7,'Anil',3),
            (8,'Kiran',3)

/*
    STEP - 3. QUERY TO GET THE DATA BY USING RIGHT JOIN.
*/
SELECT D.DepartmentName,COUNT(E.DeptId) AS EmployeeCount
FROM		#Employees E
RIGHT JOIN	#Department D
ON			E.DeptId	=	D.DeptId
GROUP BY	D.DepartmentName
ORDER BY	EmployeeCount
/*
	STEP - 4. DROP THE TEMPORARY TABLES
*/
DROP TABLE #Employees
DROP TABLE #Department

ORDERS AND PRODUCTS EXAMPLE

/*
    STEP - 1. CREATE PRODUCTS TABLE FOR THIS EXAMPLE.
*/
--First declare a table and populate the data for this example.
create table #Products (
    ProductId int primary key,
    ProductName varchar(100)
)

/*
    STEP - 2. INSERT SOME SAMPLE TEST DATA IN TO PRODUCTS TABLE.
*/
--Populating some sample data in to the above table.
insert into #Products(ProductId,ProductName)
    values  (1,'Keyboard'),
            (2,'Mouse'),
            (3,'Monitors'),
            (4,'PenDrive'),
            (5,'UPS')

/*
    STEP - 1. CREATE ORDERS TABLE FOR THIS EXAMPLE.
*/
--First declare a table and populate the data for this example.
create table #ORders (
    OrderId int primary key,
    OrderNo int,
    ProdutId int foreign key references #Products(ProductId)
)

/*
    STEP - 2. INSERT SOME SAMPLE TEST DATA IN TO ORDERS TABLE.
*/
--Populating some sample data in to the above table.
insert into #ORders(OrderId,OrderNo,ProdutId)
    values  (1,45484,1),
            (2,15978,1),
            (3,75326,2),
            (4,56214,2),
            (5,98754,2),
            (6,36948,2),
            (7,15874,3),
            (8,95384,3)

/*
    STEP - 3. QUERY TO GET THE DATA BY USING RIGHT JOIN.
*/
SELECT P.ProductName ,COUNT(O.ProdutId) AS OrderCount
FROM		#ORders O
RIGHT JOIN	#Products P
ON			P.ProductId		=	O.ProdutId
GROUP BY	P.ProductName
ORDER BY	OrderCount
/*
	STEP - 4. DROP THE TEMPORARY TABLES
*/
DROP TABLE #Products
DROP TABLE #ORders

I hope that you enjoyed this post. Please leave any feedback in the comments area and any SQL Server queries you have. I will try to give answers to them.

Happy Learning :)

Posted in SQL SERVER | Tagged | Leave a comment

SQL Server – Difference between inner join and left join

Click Here for complete List.

In this post I will explain the difference between INNER JOIN and LEFT JOIN.

First of all, let us try to understand why we have to use joins. JOINS are used to get the data from more than one tables. Let us see the scenario of Customers and their order details in an organization. The following image shows the Customers and Customer Orders tables, where some customers have not yet ordered any products.

Cusomters and Orders Table with Primary and Foreign Keys

Customers and Orders Table with Primary and Foreign Keys

INNER JOIN: Inner join returns only the matching rows from both the tables based on the join condition we use in the query.

LEFT JOIN: Left join returns the matching rows from both the tables and the non-matching rows from the first table used in the query based on the join condition.

Let’s see the complete script with example below.

/*
    STEP - 1. CREATE CUSTOMER TABLE FOR THIS EXAMPLE.
*/
--First declare a table and populate the data for this example.
create table #Customers (
    Id int primary key,
    Name varchar(100),
    Location varchar(100)
)

/*
    STEP - 2. INSERT SOME SAMPLE TEST DATA IN TO CUSTOMER TABLE.
*/
--Populating some sample data in to the above table.
insert into #Customers(Id,Name,Location)
    values  (1,'Siva','Hyderabad'),
            (2,'Komal','Bangalore'),
            (3,'Jashnak','Chennai'),
            (4,'Kumar','Delhi'),
            (5,'Anil','Bangalore'),
            (6,'Kiran','Kolkata')

/*
    STEP - 1. CREATE CUSTOMERS ORDERS TABLE FOR THIS EXAMPLE.
*/
--First declare a table and populate the data for this example.
create table #CustOrders (
    OrderId int primary key,
    OrderNo int,
    CustomerId int foreign key references #Customers(Id)
)

/*
    STEP - 2. INSERT SOME SAMPLE TEST DATA IN TO CUSTOMER ORDERS TABLE.
*/
--Populating some sample data in to the above table.
insert into #CustOrders(OrderId,OrderNo,CustomerId)
    values  (1,979879,1),
            (2,448787,2),
            (3,459787,3),
            (4,989547,2),
            (5,898454,4),
            (6,787545,2)

/*
    STEP - 3. QUERY WITH INNER JOIN. Here we will get all the matching
    rows from both the tables based on the Customer Id.
*/
SELECT		C.Name,CO.OrderNo
FROM		#Customers C
INNER JOIN	#CustOrders CO
ON			C.Id	=	CO.CustomerId

/*
    STEP - 3. QUERY WITH LEFT OUTER JOIN. Here we will get all the matching
    rows from both the tables based on the Customer Id, and also the
    non-matching rows from the customers table. i.e., Customers who did
    not placed orders yet.
*/
SELECT		C.Name,CO.OrderNo
FROM		#Customers C
LEFT JOIN	#CustOrders CO
ON			C.Id	=	CO.CustomerId

/*
	STEP - 4. DROP THE TEMPORARY TABLES
*/
DROP TABLE #Customers
DROP TABLE #CustOrders

If we understand the basics of Join and Left join, then it is easy for us to answer any questions on any type of joins.

The other type of joins
RIGHT JOIN: This is exactly opposite to left join. It returns all the matching rows from both the tables and returns the non-matching rows from the second table used in the join condition.

FULL JOIN: It returns all the matching rows from both the tables and all the non-matching rows from both the tables.

In Interviews we may face some other questions like, what is the difference between INNER JOIN  and JOIN. There is no difference, they are exactly the same. Similarly there is no difference between

LEFT JOIN and LEFT OUTER JOIN
RIGHT JOIN and RIGHT OUTER JOIN
FULL JOIN and FULL OUTER JOIN

I hope that you enjoyed this post. Please leave any feedback in the comments area and any SQL Server queries you have. I will try to give answers to them.

Happy Learning :)

Posted in SQL SERVER | Tagged | Leave a comment

SQL Server Query – To find department with highest number of employees

Click Here for complete List.

Today, Let us see how to write a SQL Server query to get the department name with the highest number of employees in that Department.

To work with this example, let us assume two tables, Department and Employees. Employees table will have a foreign key column from Department table.

I have given the complete script below, copy paste and see the execution of the query.

/*
    STEP - 1. CREATE DEPARTMENT TABLE FOR THIS EXAMPLE.
*/
--First declare a table and populate the data for this example.
create table #Department (
    DeptId int primary key,
    DepartmentName varchar(100)
)

/*
    STEP - 2. INSERT SOME SAMPLE TEST DATA IN TO DEPARTMENT TABLE.
*/
--Populating some sample data in to the above table.
insert into #Department(DeptId,DepartmentName)
    values  (1,'HR'),
            (2,'Accounts'),
            (3,'IT')

/*
    STEP - 1. CREATE EMPLOYEE TABLE FOR THIS EXAMPLE.
*/
--First declare a table and populate the data for this example.
create table #Employees (
    EmpId int primary key,
    EmployeeName varchar(100),
    DeptId int foreign key references #Department(DeptId)
)

/*
    STEP - 2. INSERT SOME SAMPLE TEST DATA IN TO EMPLOYEE TABLE.
*/
--Populating some sample data in to the above table.
insert into #Employees(EmpId,EmployeeName,DeptId)
    values  (1,'siva',1),
            (2,'kumar',1),
            (3,'komal',2),
            (4,'jashank',2),
            (5,'Arjun',2),
            (6,'Ram',2),
            (7,'Anil',3),
            (8,'Kiran',3)

--LET US SEE THE EMPLOYEES COUNT BY EACH DEPARTMENT
SELECT D.DepartmentName,COUNT(E.DeptId) AS EmployeeCount
FROM		#Employees E
JOIN		#Department D
ON			E.DeptId	=	D.DeptId
GROUP BY	D.DepartmentName

/*
    STEP - 3. QUERY TO GET DEPARTMENT WITH HIGHEST NO OF EMPLOYEES
*/
SELECT TOP 1 D.DepartmentName,COUNT(E.DeptId) AS EmployeeCount
FROM		#Employees E
JOIN		#Department D
ON			E.DeptId	=	D.DeptId
GROUP BY	D.DepartmentName
ORDER BY	COUNT(E.DeptId) DESC

/*
	STEP - 4. DROP THE TEMPORARY TABLES
*/
DROP TABLE #Employees
DROP TABLE #Department

In the above query, we have use inner join to get the result. We will learn about JOINS in the next post.

I hope that this post helps as a quick note for you. Please leave any feedback in the comments area and any SQL Server queries you have. I will try to give answers to them.

Happy Learning :)

Posted in SQL SERVER | Tagged | Leave a comment