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

SQL Server Query – To find rows that contain only numerical data

Click Here for complete List.

In this post let us see a SQL Query to retrieve only the numeric rows from the table.

We use the ISNUMERIC function available in SQL Server. It returns 1 when the input expression is a valid numeric data type, else it returns 0.

For a complete list of valid numeric data types in SQL Server, Click Here.

To continue with this post, simply copy paste the below script in the required database and see the execution.

/*
    STEP - 1. CREATE A TABLE FOR THIS EXAMPLE.
*/
--First declare a table and populate the data for this example.
--Let us take the example of some random values for this post
declare @temptable as table(
    Id int identity primary key,
    Value varchar(100)
)

/*
    STEP - 2. INSERT SOME SAMPLE TEST DATA.
*/
--Populating some sample data in to the above table.
insert into @temptable(Value)
    values  ('siva'),
            ('kumar'),
            ('reddy'),
            ('1978'),
            ('komal'),
            ('2010'),
            ('jashank'),
            ('2012')

/*
    STEP - 3. LET SEE THE RESULT WITH OUT APPLYING IS NUMERIC FUNCTION
*/
select * from @temptable

/*
    STEP - 4. FINAL RESULT AFTER APPLYING NUMERIC FUNCTION
*/
select * from @temptable where ISNUMERIC(value)=1

/*
In the same way if we want to filter non-numeric values simple
change the value for isnumeric function
*/
select * from @temptable where ISNUMERIC(value)=0

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