What is meant by JOIN in SQL Server?
What are different types of JOINS in SQL Server?
What is meant by JOIN in SQL Server?
Joins in SQL Server are used to retrieve data from multiple tables based on logical relationships between the tables.
A Join condition defines the way two tables are related in a query by.
1- Specifying the column from each of the table to be used for the join. A typical join condition specifies a foreign key from one table and its associate key in other table.
2- Specifying a logical operator (i.e =, <>, <) to be used in comparing values from the columns.
What are different types of JOINS in SQL Server?
Types of JOINS in SQL Server.
Inner Join
Equi-join
Natural Join
Outer Join
Left outer Join
Right outer join
Full outer join
Cross Join
Self Join
CREATE DATABASE TEACHMESQLSERVER
GO
USE TEACHMESQLSERVERGO
GO
CREATE TABLE DEP(DEPID INT, DEPNAME VARCHAR(MAX))
CREATE TABLE EMP(EMPID INT, EMPNAME VARCHAR(MAX), DEPID INT)
GO
INSERT INTO DEP VALUES(1,'SALES'),(2,'IT'),(3,'HR'),(4,'MARKETING'),(5,'ACCOUNT')
GO
INSERT INTO EMP VALUES(1,'GHANESH',1)
INSERT INTO EMP VALUES(2,'PRASAD',2)
INSERT INTO EMP VALUES(3,'GAUTAM',3)
INSERT INTO EMP VALUES(4,'OJAL',1)
INSERT INTO EMP VALUES(5,'YOGITA',1)
INSERT INTO EMP VALUES(6,'ANVIE',2)
INSERT INTO EMP VALUES(7,'SAURABH',3)
INSERT INTO EMP VALUES(8,'KAPIL',4)
INSERT INTO EMP(EMPID,EMPNAME) VALUES(9,'ATUL')
INSERT INTO EMP(EMPID,EMPNAME) VALUES(10,'SACHIN')
GO
SELECT * FROM DEP
GO
SELECT * FROM EMP
INNER JOIN
Inner join returns the record when at least one match is found in both the tables. Inner Join can be Equi Join and Non Equi Join.
Equi- Join
In Equi join we only use Equality (=) operator in query for comparing the values from columns. Equi join can be written for Outer Join also.
We can write the query in two ways, first method is the old way of writing INNER join query, second statement is the new style of writing INNER join query.
SELECT DEPNAME,EMPNAME FROM DEP ,EMP WHERE DEP.DEPID=EMP.DEPID
OR
SELECT DEPNAME,EMPNAME FROM DEP INNER JOIN EMP ON DEP.DEPID=EMP.DEPID
As you can see from the result set we are getting only 8 records but we have 10 employees in our EMP table. Our query is returning only those records for which at least one match was found. EMPNAME ATUL and SACHIN were not returned because there was no match found in DEP table for these two records.
Non Equi Join
In Non Equi join we don’t use = operator but we use other available operator (i.e BETWEEN) in query for comparing the values from columns.
Outer Join
We have discussed Inner joins which return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows if there is no match from both the tables. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.
SQL Server uses the following ISO keywords for outer joins specified in a FROM clause:
LEFT OUTER JOIN or LEFT JOIN- All rows are retrieved from the left table (DEP) referenced with a left outer join plus unmatched information from EMP table.
USE TEACHMESQLSERVER
SELECT DEPNAME,EMPNAME FROM DEP LEFT JOIN EMP ON DEP.DEPID=EMP.DEPID
As you can see from the result set our query is returning all records from LEFT table (DEP), Based on Join condition If match will be found in other table then it will return the value , if no match is found in other table in that case it fill the column value with NULL.
RIGHT OUTER JOIN or RIGHT JOIN - All rows are retrieved from the right table (EMP) referenced in a right outer join plus unmatched information from DEP table.
USE TEACHMESQLSERVER
SELECT DEPNAME,EMPNAME FROM DEP RIGHT JOIN EMP ON DEP.DEPID=EMP.DEPID
As you can see from the result set our query is returning all records from RIGHT table (EMP),Based on Join Condition If match will be found in other table then it will return the value, if no match is found in other table in that case it fill the column value with NULL.
FULL OUTER JOIN or FULL JOIN- All rows from both tables are returned in a full outer join.
USE TEACHMESQLSERVER
SELECT DEPNAME,EMPNAME FROM DEP FULL JOIN EMP ON DEP.DEPID=EMP.DEPID
As you can see from the result set our query is returning all records from LEFT table (DEP) and RIGHT table (EMP), Based on Join condition If match will be found in other table then it will return the value , if no match is found in either table in that case it fill the column value with NULL.
Cross Join
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. If a WHERE Clause is added, the cross join behaves as an Inner Join.
USE TEACHMESQLSERVER
SELECT DEPNAME,EMPNAME FROM DEP, EMP
Total records returned the query is 50 because DEP table has 5 records and EMP table has 10 records.
Self Join
When you join a table with itself then this join is known as Self Join.
I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.
Aim :- This article will make you learn about Joins in SQL Server. I will provide you some Pictorial view and examples of Joins in SQL Server. I will be discussing on the 2 sections –
Define joins in SQL Server?
Different types of joins in SQL Server?
Description :- Now, I am going to explain you each section of joins in SQL Server into detail.
Question #1. What is a Join?
We all know how to fetch data from a SQL table. It is very easy – Just fire a Select query. You will get your desired output.
But what you will do if you require data which is present in 2 or more SQL tables. This gives rise to a powerful SQL clause known as Join. Joins in SQL Server are used to combine rows from two or more tables. This join operation is based on a common field between the tables.
Question #2. What are different types of joins in sql server?
Generally we have three types of joins. They are as following –
Inner Join.
Outer Join.
Self Join.
These 3 categories are than further sub-divided into various types of Joins in SQL Server. I am mentioning below all the joins you will hear in your SQL life.
Inner Join is divided into – Equi Join || Natural Join || Cross Join.
Outer Join is divided into – Left Outer Join || Right Outer Join || Full Outer Join.
I will provide some brief information about each Joins in SQL Server. Also for better understanding, I will show all types of joins in Pictorial format. The pictorial format will give more idea to understand thefunctionality of each join. I hope you will like and enjoy learning Joins in SQL Server this way.
Type 1. Inner Join –
An Inner join in SQL Server returns “Matched Rows” from the multiple tables. Just follow the below picture, it will clearly show how inner join performs operations internally. Inner join can be further sub divided into 3 types –
Equi Join.
Natural Join.
Cross Join.
Equi Join – The Equi join in SQL Server is used to display all the “Matched Rows” from the Joined tables and also display Redundant data. In this join, we need to use * sign to join the table.
Natural Join – The Natural Join in SQL Server is same as Equi Join but the only difference is that it willnot display Redundant data.
Cross Join – This join is a Cartesian join and does not necessitate any condition to join. The result set contains records that are multiplication of record number from both the tables. In Simple words, Cross join in SQL Server gives a Cartesian product of multiple tables.
Type 2. Outer Join –
An Outer join in SQL Server returns “Matched Rows” as well as “Unmatched Rows” from the multiple tables. Outer join is classified into 3 types –
Left Outer Join.
Right Outer Join.
Full Outer Join.
Left Outer Join – A Left Outer Join in SQL Server returns the “Matched Rows” from multiple tables and “Non Matched Rows” from Left side table. Follow the above picture for more understanding.
Right Outer Join – A Right Outer Join in SQL Server returns the “Matched Rows” from multiple tables and “Non Matched Rows” from Right side table. Follow the above picture for more understanding.
Full Outer Join – A Full Outer Join returns “Matched Rows” from multiple tables and also “Non Matched Rows” from multiple tables. Follow the above picture for more understanding.
Summary :- In this article on Joins in SQL Server, we learned –
What is a Join and Why we use this Clause.
What are different types of Joins available in SQL Server.
Pictorial representation of each Join.
SQL CROSS JOIN
In SQL Server, Cross Join returns the cartesian product of both the tables. Cross Join does not require any common column to join two table.
Cartesian product means Number of Rows present in Table 1 Multiplied by Number of Rows present in Table 2
SQL Cross Join Syntax
The basic syntax of the SQL Server Cross Join is as follows:
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
CROSS JOIN
Table2
--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1, Table2
In this article we will show you, How to write Cross Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.
Data present in the Employee Table is:
Data present in the Department Table is:
SQL Cross Join Example
The following SQL Query will display the Cartesian product of the columns present in Employees and Department tables.
T-SQL CODE
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
CROSS JOIN
[Department] AS Dept
--OR We Can Write
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
[Department] AS Dept
OUTPUT
If you observe the above screenshot, It is displaying 120 records. It means 15 rows from the Employee multiplies by 8 rows in the Department table
Using Where Clause in SQL Cross Join
SQL allows us to use the Where Clause to restrict the number of rows returned by the Cross Join
T-SQL CODE
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
[Department] AS Dept
WHERE Dept.[DepartmentName] = 'Software Developer'
OUTPUT
SQL FULL JOIN
In SQL Server, Full Join returns all the records (or rows) present in both Left table and the right table. All the Unmatched rows will be filled with NULL Values.
SQL Full Join Syntax
The basic syntax of the SQL Server Full Join is as follows:
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
FULL OUTER JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column
--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
FULL JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column
TIP: In SQL Server, Full Outer join can also be called as Full Join so it is optional to use the Outer Keyword.
Let us see the visual representation of the Full join for better understanding.
From the above image you can understand easily that, Full Outer join displays all the records present in Table 1 and Table 2
In this article we will show you, How to write Full Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.
Data present in the Employee Table is:
Data present in the Department Table is:
SQL Full Join – Selecting All Columns
The following SQL Query will display all the columns and rows present in Employees and Department tables
T-SQL CODE
SELECT *
FROM [Employee]
FULL OUTER JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]
OUTPUT
If you observe the above screenshot, Although We have 15 records in Employee table, Full Join is displaying 17 records. This is because, there are two records in the Department table i.e., Department Id 3, 4 (Module Lead and Team Lead) so 15 + 2 = 17 total records.
For Department Id 3, 4 (Module Lead and Team Lead) there is no matching records in Employees table so they are simply replaced by NULLS.
For [DepartID], id, [Department Name] it is displaying NULL Values for the ID number 10, 11, 14 and 15 . This is because, Department Id for them in Employee table are NULLS so there is no matching records in right table.
NOTE: The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns so, Please avoid SELECT * Statements in Full Join
SQL Full Join – Selecting Few Columns
Please place the required columns after the SELECT Statement to avoid unwanted columns
T-SQL CODE
SELECT [FirstName]
,[LastName]
,[DepartmentName]
FROM [Employee]
FULL JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]
OUTPUT
Above query will perfectly work as long as the column names from both tables (Employee and Department) are different like above. What happens if we have same Column names in both the tables? Well, you will end up in a mess. Let us see how to resolve the issue.
The following query is using table name before the column name and also used theALIAS names to rename the column names. By this approach we can inform the Database that we are looking for columns belonging to particular table.
T-SQL CODE
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
FULL OUTER JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [DepartmentName] ASC
OUTPUT
Although we don’t have any common column names, Imagine we have [DepartmentName] column in Employee as well and we want to display Department Columns in both the tables.
We can simply write as
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Emp.[DepartmentName] AS [Employee Department]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
FULL JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [DepartmentName] ASC
NOTE: It is always best practice to use the table name before the Column name in Joins. For example, SELECT Employee.Firstname
SQL INNER JOIN
In SQL, INNER JOIN returns the records (or rows) present in both tables, If there is at least one match between columns.
Or we can Simply say
INNER JOIN returns the records (or rows) present in both tables as long as the Condition after the ON Keyword is TRUE.
SQL INNER JOIN Syntax
The basic syntax of the SQL Server Inner Join is as follows:
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
INNER JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column
--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column
In SQL, Inner join is the default join so it is optional to use the INNER Keyword.
Let us see the visual representation of the Inner join for better understanding.
From the above image you can understand easily that, Inner join only displays the matching records from Table 1 and Table 2 (Like an Intersect in Math)
In this article we will show you, How to write Inner Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.
Data present in the Employee Table is:
Data present in the Department Table is:
SQL Inner Join – Selecting All Columns
The following SQL Query will display all the columns present in Employees and Department tables
T-SQL CODE
SELECT *
FROM [Employee]
JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]
OUTPUT
If you observe the above screenshot, Although we have 15 records in Employee table, Inner join is displaying 11 records. This is because, Department Id for the remaining 4 records (i., ID number 10, 11, 14 and 15) in Employee table are NULLS.
NOTE: The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns so, Please avoid SELECT * Statements in INNER JOINS
SQL Inner Join – Selecting Few Columns
Please place the required columns after the SELECT Statement to avoid unwanted columns
T-SQL CODE
SELECT [FirstName]
,[LastName]
,[DepartmentName]
FROM [Employee]
INNER JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]
OUTPUT
Above query will perfectly work as long as the column names from both tables (Employee and Department) are different like above. What happens if we have same Column names in both the tables? Well, you will end up in a mess. Let us see how to resolve the issue.
The following query is using table name before the column name and also used theALIAS names to rename the column names. By this approach we can inform the Database that we are looking for columns belonging to particular table.
T-SQL CODE
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
INNER JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [FirstName] ASC
OUTPUT
Although we don’t have any common column names, Imagine we have [DepartmentName] column in Employee as well and we want to display Department Columns in both the tables.
We can simply write as
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Emp.[DepartmentName] AS [Employee Department]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
INNER JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [FirstName] ASC
NOTE: It is always best practice to use the table name before the Column name in Joins. For example, SELECT Employee.Firstname
SQL LEFT JOIN
In SQL Server, Left Join returns all the records (or rows) present in the Left table and matching rows from the right table.
NOTE: All the Unmatched rows from the right table will be filled with NULL Values.
SQL LEFT JOIN Syntax
The basic syntax of the SQL Server Left Join is as follows:
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
LEFT OUTER JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column
--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
LEFT JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column
TIP: In SQL Server, Left Outer join can also be called as Left Join so it is optional to use the Outer Keyword.
Let us see the visual representation of the Left Outer join for better understanding.
From the above image you can understand easily that, Left Outer join displays all the records present in Table 1 and matching records from Table 2
In this article we will show you, How to write Left Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.
Data present in the Employee Table is:
Data present in the Department Table is:
SQL Left Join – Selecting All Columns
The following SQL Query will display all the columns present in Employees and Department tables
T-SQL CODE
SELECT *
FROM [Employee]
LEFT OUTER JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]
OUTPUT
If you observe the above screenshot, We have 15 records in Employee table, Left Outer Join is displaying 15 records but for [DepartID], id, [Department Name] it is displaying NULL Values for the ID number 10, 11, 14 and 15 . This is because, Department Id for them in Employee table are NULLS so there is no matching records in right table.
NOTE: The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns so, Please avoid SELECT * Statements in Left Join
SQL Left Join – Selecting Few Columns
Please place the required columns after the SELECT Statement to avoid unwanted columns
T-SQL CODE
SELECT [FirstName]
,[LastName]
,[DepartmentName]
FROM [Employee]
LEFT JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]
OUTPUT
Above query will perfectly work as long as the column names from both tables (Employee and Department) are different like above. What happens if we have same Column names in both the tables? Well, you will end up in a mess. Let us see how to resolve the issue.
The following query is using table name before the column name and also used theALIAS names to rename the column names. By this approach we can inform the Database that we are looking for columns belonging to particular table.
T-SQL CODE
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
LEFT JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [DepartmentName] ASC
OUTPUT
Although we don’t have any common column names, Imagine we have [DepartmentName] column in Employee as well and we want to display Department Columns in both the tables.
We can simply write as
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Emp.[DepartmentName] AS [Employee Department]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
LEFT JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [DepartmentName] ASC
NOTE: It is always best practice to use the table name before the Column name in Joins. For example, SELECT Employee.Firstname
SQL RIGHT JOIN
In SQL Server, Right Join returns all the records (or rows) present in the Right table and matching rows from the Left table.
NOTE: All the Unmatched rows from the Left table will be filled with NULL Values.
SQL RIGHT JOIN Syntax
The basic syntax of the SQL Server Right Join is as follows:
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
RIGHT OUTER JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column
--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
RIGHT JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column
TIP: In SQL Server, Right Outer join can also be called as Right Join so it is optional to use the Outer Keyword.
Let us see the visual representation of the Right Outer join for better understanding.
From the above image you can understand easily that, Right Outer join displays all the records present in Table 2 and matching records from Table 1
In this article we will show you, How to write Right Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.
Data present in the Employee Table is:
Data present in the Department Table is:
SQL Right Join – Selecting All Columns
The following SQL Query will display all the columns present in Employees and Department tables
T-SQL CODE
SELECT *
FROM [Employee]
RIGHT OUTER JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]
OUTPUT
If you observe the above screenshot, Although we have 15 records in Employee table, Right join is displaying 13 records. This is because, Department Id for 14th and 15th records in Employee table are NULLS so there is no matching records in right table.
If you observe the 8th and 9th records, they are displaying NULL values because in Employee table there are no matching records for Department Id 3, 4 (Module Lead and Team Lead) in the Department table so they are simply replaced by NULLS.
NOTE: The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns so, Please avoid SELECT * Statements in Right Join
SQL Right Join – Selecting Few Columns
Please place the required columns after the SELECT Statement to avoid unwanted columns
T-SQL CODE
SELECT [FirstName]
,[LastName]
,[DepartmentName]
FROM [Employee]
RIGHT JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]
OUTPUT
Above query will perfectly work as long as the column names from both tables (Employee and Department) are different like above. What happens if we have same Column names in both the tables? Well, you will end up in a mess. Let us see how to resolve the issue.
The following query is using table name before the column name and also used theALIAS names to rename the column names. By this approach we can inform the Database that we are looking for columns belonging to particular table.
T-SQL CODE
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
RIGHT JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [FirstName] ASC
OUTPUT
Although we don’t have any common column names, Imagine we have [DepartmentName] column in Employee as well and we want to display Department Columns in both the tables.
We can simply write as
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Emp.[DepartmentName] AS [Employee Department]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
RIGHT JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [FirstName] ASC
NOTE: It is always best practice to use the table name before the Column name in Joins. For example, SELECT Employee.Firstname
SQL SELF JOIN
In SQL Server, Self Join is used to Join the Table 1 to Itself. If we have the Primary key and Foreign key in the same table then, we can use this Self join to connect them.
SQL Self Join Syntax
The basic syntax of the SQL Server Self Join is as follows:
SELECT Tab1.Column(s), Tab2.Column(s),
FROM Table1 AS Tab1,
Table1 AS Tab2
NOTE: Alias Table Name is mandatory for Self Join
In this article we will show you, How to write Self Join in SQL Server 2014. For this, We are going to use Employees table present in our [SQL Server Tutorials] Database.
Data present in the Employees Table is:
From the above screenshot you can observe that we have 15 different employees and each person belongs to Department and we have the Foreign key column DepartID.
SQL Self Join Example
In general, Every Department will have one Department Head and he will definitely belongs to one of the Employee. To get the Department Head name we have to use the Self Join using the Primary key and Foreign key. For example, The following SQL Query will display the Name of the Department Head for each and every Employee present in the Employees table.
T-SQL CODE
SELECT EMP1.[FirstName]
,EMP1.[LastName]
,EMP1.[Department Name]
,EMP2.[FirstName] +' '+ EMP2.[LastName] AS [Department Head]
FROM [Employees] AS EMP1, [Employees] AS EMP2
WHERE EMP1.DepartID = EMP2.ID
OUTPUT
If you observe the above screenshot, Rob Walter is not only Sr. Software Developer but also Department Head for the remaining Sr. Software Developer in an organization.
JOIN ORDER cans Increase Performance
Introduction
All the developer is very much concern related to performance. If someone say that this increase performance all the developer are running behind it. It is not a bad practice at all. Rather as per my point of view we must span all our effort related improve the performance of query.
“One common question that we find that, if we change the ordering of table join in case of inner join will effect or increase performance”
To understand it lets take a simple example of Inner join. There is two tables named Table-A and Table-B. We can us the Inner Join on both the table.
Like this
FROM [Table-A] AS a INNER JOIN [Table-B] AS b ON a.IDNO = b.IDNO
OR
FROM [Table-B] AS a INNER JOIN [Table-A] AS b ON a.IDNO = b.IDNO
Which one is best for performance?
To answer this question we all know that whenever a SQL Query is executed the MS SQL server create several query plans with different join Order and choose the best one.
That means the Join order that we are writing in the query may not be executed by execution plan. May be different join order is used by the execution plan. In the above case the execution plan decide which Join order he will chose depends on best possible costing of execution.
Here [Table-A] JOIN [Table-B] or [Table-B] JOIN [Table-A], MS SQL Server knows it well that both are same.
To understand it Details Lets take an Example
Step-1 [ Create Base Table and Insert Some Records ]
-- Item Master
IF OBJECT_ID(N'dbo.tbl_ITEMDTLS', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_ITEMDTLS];
END
GO
CREATE TABLE [dbo].[tbl_ITEMDTLS]
(
ITEMCD INT NOT NULL IDENTITY PRIMARY KEY,
ITEMNAME VARCHAR(50) NOT NULL
)
GO
-- Inserting Records
INSERT INTO [dbo].[tbl_ITEMDTLS]
(ITEMNAME)
VALUES ('ITEM-1'),('ITEM-2'),('ITEM-3');
-- Item UOM Master
IF OBJECT_ID(N'dbo.tbl_UOMDTLS', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_UOMDTLS];
END
GO
CREATE TABLE [dbo].[tbl_UOMDTLS]
(
UOMCD INT NOT NULL IDENTITY PRIMARY KEY,
UOMNAME VARCHAR(50) NOT NULL
)
GO
-- Inserting Records
INSERT INTO [dbo].[tbl_UOMDTLS]
(UOMNAME)
VALUES ('KG'), ('LTR'), ('GRM');
GO
-- Transaction Table
IF OBJECT_ID(N'dbo.tbl_SBILL', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_SBILL];
END
GO
CREATE TABLE [dbo].[tbl_SBILL]
(
TRID INT NOT NULL IDENTITY PRIMARY KEY,
ITEMCD INT NOT NULL,
UOMCD INT NOT NULL,
QTY DECIMAL(18,3) NOT NULL,
RATE DECIMAL(18,2) NOT NULL,
AMOUNT AS QTY * RATE
);
GO
-- Foreign Key Constraint
ALTER TABLE [dbo].[tbl_SBILL]
ADD CONSTRAINT FK_ITEM_tbl_SBILL FOREIGN KEY(ITEMCD) REFERENCES [dbo].[tbl_ITEMDTLS](ITEMCD);
GO
ALTER TABLE [dbo].[tbl_SBILL]
ADD CONSTRAINT FK_UOMCD_tbl_SBILL FOREIGN KEY(UOMCD) REFERENCES [dbo].[tbl_UOMDTLS](UOMCD);
-- Insert Records
INSERT INTO [dbo].[tbl_SBILL]
(ITEMCD, UOMCD, QTY, RATE)
VALUES (1, 1, 20, 2000),(2, 3, 23, 1400);
Step-2 [ Now Make Some JOIN ]
SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD,
c.UOMNAME, b.QTY, b.RATE, b.AMOUNT
FROM [dbo].[tbl_ITEMDTLS] AS a
INNER JOIN [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD
INNER JOIN [dbo].[tbl_UOMDTLS] AS c ON b.UOMCD = c.UOMCD;
Here [tbl_ITEMDETAILS] JOIN [tbl_SALES] JOIN [tbl_UOMDETAILS]
If we look at the Execution Plan
We find that
[tbl_SALES] JOIN [tbl_ITEMDETAILS] JOIN [tbl_UOMDETAILS]
Step-2 [ Now we need to Force Order Hint to maintain Join Order ]
SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD,
c.UOMNAME, b.QTY, b.RATE, b.AMOUNT
FROM [dbo].[tbl_ITEMDTLS] AS
INNER JOIN [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD
INNER JOIN [dbo].[tbl_UOMDTLS] AS c ON b.UOMCD = c.UOMCD
OPTION ( QUERYRULEOFF JoinCommute);
For this we need the FORCE ORDER Hint.
The query optimizer uses different rules to evaluate different plan and one of the rules is called JoinCommute. We can turn it off using the undocumented query hint QUERYRULEOFF.
Condition in ON cluase of OUETR JOIN Effects
Introduction
Where we are making JOIN, a lot of developer uses extra condition within the ON clause of the JOIN. In my review I find that some developer added a lot of condition in the ON clause (they are including WHERE condition within the ON clues of the JOIN).
Please remind it that the extra condition of ON clause of JOIN can affect the OUTER JOIN. This article is related to it.
What MS says
First we look at what MS Says about it.
"Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join."
Example of Extra Condition
To understand it properly am taking an example of that.
Step-1 [ Create the Base Table ]
IF OBJECT_ID('my_TABLE1') IS NOT NULL
BEGIN
DROP TABLE my_TABLE1;
END
GO
CREATE TABLE my_TABLE1
(ID INT NOT NULL IDENTITY PRIMARY KEY,
SNAME VARCHAR(50) NOT NULL);
GO
IF OBJECT_ID('my_TABLE2') IS NOT NULL
BEGIN
DROP TABLE my_TABLE2;
END
GO
CREATE TABLE my_TABLE2
(ID INT NOT NULL IDENTITY PRIMARY KEY,
SECTION VARCHAR(1) NOT NULL);
GO
Step-2 [ Insert Records ]
INSERT INTO my_TABLE1
(SNAME)
VALUES ('JOYDEEP'),
('SUKAMAL'),
('TUHIN'),
('SANGRAM'),
('ROOHUL');
GO
INSERT INTO my_TABLE2
(SECTION)
VALUES ('A'),
('B'),
('A');
Step-3 [ Make the INNER JOIN]
SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
INNER JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A'
SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
INNER JOIN my_TABLE2 b ON a.ID = b.ID
WHERE b.SECTION = 'A'
Both of the Queries display the same output
ID SNAME SECTION
1 JOYDEEP A
3 TUHIN A
Step-4 [ Make the LEFT JOIN ]
SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
LEFT JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A'
Output:
ID SNAME SECTION
1 JOYDEEP A
2 SUKAMAL NULL
3 TUHIN A
4 SANGRAM NULL
5 ROOHUL NULL
SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
LEFT JOIN my_TABLE2 b ON a.ID = b.ID
WHERE b.SECTION = 'A'
Output:
ID SNAME SECTION
1 JOYDEEP A
3 TUHIN A
Observation -1
In case of INNER JOIN both the query is fine and work perfectly.
But in case of LEFT JOIN both the query react differently.
In case of LEFT OUTER JOIN first query the SECTION = 'A' condition works on before JOIN. It retrieves the result from my_TABLE2 and LEFT OUTER JOIN with my_TABLE1 and it does not affect the my_TABLE1.
When we add the WHERE clause it effects of the complete result set after JOIN and filters the records.
Observation -2
Now we see the actual execution plan of both query (INNER JOIN and LEFT JOIN).
SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
INNER JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A';
SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
LEFT JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A';
In case of Query-2 the hash match operator is used as join. In Query-2 by optimizer will be switched to inner join instead left outer join.
INNER or LEFT JOIN for Performance
In this article I am trying to discuss a very common scenario that between INNER JOIN and LET JOIN which one is increase the performance and also discuss how to boost the join performance.
First look at the definition of both INNER and LEFT JOIN.
INNER JOIN
The INNER JOIN keyword return rows when there is at least one match in both tables.
LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (Table-1), even if there are no matches in the right table (Table-2).
General rules
INNER JOIN are usually faster than left joins, but if we need a left join for unmatched results then an inner join will not give you the results that we need.
If we are filtering on the "right" table in a LEFT JOIN, then indeed we should use an INNER JOIN instead, and we may get better performance.
What exactly we have to do
Only thing is that we must look at the execution plan very carefully as the Query use the HASH or MERGE Join operator. As the MERGE JOIN which is more efficient than HASH JOIN for fairly small, sorted tables.
The extra work the left join performs is when a row in the left table has no matching rows in the right table; the result set row contains NULL values for all the selected columns coming from the right table. If this is what we want, then use this type of join and it is the real performance killer.
So it matters depends on situation to situation.
Here are an example of INNER JOIN and a LEFT JOIN which gives us the same result sets.
CREATE TABLE #tem_Tab1
(emcd INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
empname VARCHAR(50) NOT NULL)
INSERT INTO #tem_Tab1
(empname)
VALUES ('Joydeep'), ('Sangram'), ('Sudip'), ('Tuhin')
CREATE TABLE #tem_Tab2
(emcd INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
empsal DECIMAL(20,2) NOT NULL)
INSERT INTO #tem_Tab2
(empsal)
VALUES (10000.00), (23000.00), (12000.00)
SELECT * FROM #tem_Tab1
SELECT * FROM #tem_Tab2
SELECT #tem_Tab1.emcd, #tem_Tab1.empname, #tem_Tab2.empsal
FROM #tem_Tab1 INNER JOIN #tem_Tab2 ON #tem_Tab1.emcd=#tem_Tab2.emcd
SELECT #tem_Tab1.emcd, #tem_Tab1.empname, #tem_Tab2.empsal
FROM #tem_Tab1 LEFT JOIN #tem_Tab2 ON 1=1
WHERE #tem_Tab1.emcd=#tem_Tab2.emcd
Look at the execution plan of both the query and we find the same execution plan for both the query.
To Boost the Join performance
1. When joining two or more table performance increased, if join columns have index.
2. Foreign keys are not automatically indexed. So if you ever plan to join a table to the table with the foreign key, using the foreign key as the linking column, then we should consider adding an index to the foreign key column. An index on a foreign key column can substantially boost the performance of many joins.
3. For best join performance, the indexes on the columns being joined should ideally be numeric data types, not CHAR or VARCHAR, or other non-numeric data types.
4. For maximum performance when joining two or more tables, the indexes on the columns to be joined should have the same data type, and ideally, the same width.
5. If our join is slow, and currently includes hints, remove the hints to see if the optimizer can do a better job on the join optimization.
6. One of the best ways to boost JOIN performance is to ensure that the Joined tables include an appropriate WHERE clause to minimize the number of rows that need to be joined.
7. In the SELECT statement that creates your JOIN, don't use an * to return all of the columns in both tables.
8. If you have the choice of using a JOIN or a subquery to perform the same task, generally the JOIN (often an OUTER JOIN) is faster.
CROSS APPLY Vs OUTER APPLY
In my previous article "CROSS APPLY on SQL Server 2005", I already mentioned the definition of cross apply with proper example.
Reference link related to article "CROSS APPLY on SQL Server 2005"
http://sqlknowledgebank.blogspot.in/2012/05/cross-apply-on-sql-2005.html
In this article I am trying to discuss related to different formation of APPLY or we can say it CROSS APPLY Vs OUTER APPLY.
APPLY has two forms
1. CROSS APPLY
2. OUTER APPLY
CROSS APPLY
Think it as an INNER JOIN.
The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table-valued functions or table expression. In other words, the right table expression returns rows for left table expression match only.
OUTER APPLY
Think it as a LEFTOUTER JOIN.
The OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table-valued functions or table expression. For those rows for which there are no corresponding matches in right table-valued functions or table expression, it contains NULL values in columns of right table expression.
To understand it properly we just take an example of both CROSS APPLY and OUTER APPLY
Step-1 [ Create the Environment ]
CREATE TABLE tbl_Dep
(DepID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
DepName VARCHAR(50) NOT NULL)
GO
INSERT INTO tbl_Dep
(DepName)
VALUES ('Developemnt')
GO
INSERT INTO tbl_Dep
(DepName)
VALUES ('Management')
GO
INSERT INTO tbl_Dep
(DepName)
VALUES ('Logistic')
CREATE TABLE tbl_Emp
(EmpID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EmpName VARCHAR(50) NOT NULL,
DepID INT)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Joydeep Das',1)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Tuhin Shina',1)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Sangram Jit',1)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Bhola',1)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Sudip Das',2)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Mithun deshi',3)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Rajesh Keshri',NULL)
GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Sukamal Jana',NULL)
GO
-- Table Valued Function
CREATE FUNCTION dbo.fnGetDept
(@p_DepID AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT DepID,
DepName
FROM tbl_Dep
WHERE DepID = @p_DepID
)
GO
SELECT * FROM tbl_Emp
GO
EmpID EmpName DepID
1 Joydeep Das 1
2 Tuhin Shina 1
3 Sangram Jit 1
4 Bhola 1
5 Sudip Das 2
6 Mithun deshi 3
7 Rajesh Keshri NULL
8 Sukamal Jana NULL
Step-2 [ CROSS APPLY ]
SELECT a.EmpID,
a.EmpName,
b.DepID,
b.DepName
FROM tbl_Emp a
CROSS APPLY dbo.fnGetDept(a.DepID)b
Output
EmpID EmpName DepID DepName
1 Joydeep Das 1 Developemnt
2 Tuhin Shina 1 Developemnt
3 Sangram Jit 1 Developemnt
4 Bhola 1 Developemnt
5 Sudip Das 2 Management
6 Mithun deshi 3 Logistic
Please look at the output as the Employee named "Rajesh Keshri" with Emp ID: 7 and "Sukamal Jana" with Emp ID 8, not appears in the list as the CROSS APPLY works like INNER JOIN.
Step-3 [ OUTER APPLY ]
SELECT a.EmpID,
a.EmpName,
b.DepID,
b.DepName
FROM tbl_Emp a
OUTER APPLY dbo.fnGetDept(a.DepID)b
GO
Output
EmpID EmpName DepID DepName
1 Joydeep Das 1 Developemnt
2 Tuhin Shina 1 Developemnt
3 Sangram Jit 1 Developemnt
4 Bhola 1 Developemnt
5 Sudip Das 2 Management
6 Mithun deshi 3 Logistic
7 Rajesh Keshri NULL NULL
8 Sukamal Jana NULL NULL
Here the employee named "Rajesh Keshri" with Emp ID: 7 and "Sukamal Jana" with Emp ID 8 appears but there Dept ID and Dep Name Contains NULL values. So it looks like LEFT OUTER JOIN.
Join HINT
JOIN HINT in SQL, What and When:
Joint hint specifies that the query optimizer enforced the join strategy before joining two tables.
By default SQL server takes the best execution plan before joining table's objects, be careful using joint hint in your SQL statement until you know them very well.
The hint is using in join to increase the performance of execution of statement. But sometimes it gives you reverse action also.
It applies to SELECT, DELETE and UPDATE statements.
Syntax is
<join_hint> ::=
{ LOOP | HASH | MERGE | REMOTE }
In this article I try to explain about the different types of hint used in SQL server.
LOOP Joint Hint:
This is the simplest form of LOOP Join. Suppose you have Tabe1 Join with Table2 by LOOP join hint.
The algorithm is motioned bellow.
FOR each row R1 in the outer table
FOR each row R2 in the inner table
IF R1 joins with R2
return (R1, R2)
Example:
SELECT a.*, b.*
FROM Table1 a
INNER LOOP JOIN Table2 b ON a.roll=b.roll
WHERE a.roll<=12
HASH Joint hint:
Hash joins parallelize and scale better than any other join and are great at maximizing throughput in data warehouses.
The hash join executes in two phases: build and probe. During the build phase, it reads all rows from the first input (often called the left or build input), hashes the rows on the equijoin keys, and creates an in-memory hash table. During the probe phase, it reads all rows from the second input (often called the right or probe input), hashes these rows on the same equijoin keys, and looks or probes for matching rows in the hash table. Since hash functions can lead to collisions (two different key values that hash to the same value), we typically must check each potential match to ensure that it really joins.
The algorithm is motioned bellow.
FOR each row R1 in the build table
BEGIN
calculate hash value on R1 join key(s)
insert R1 into the appropriate hash bucket
END
FOR each row R2 in the probe table
BEGIN
calculate hash value on R2 join key(s)
FOR each row R1 in the corresponding hash bucket
IF R1 joins with R2
return (R1, R2)
END
Example:
SELECT a.*, b.*
FROM Table1 a
INNER HASH JOIN Table2 b ON a.roll=b.roll
WHERE a.roll<=12
MERGE Join Hint:
The merge join works by simultaneously reading and comparing the two sorted inputs one row at a time. At each step, we compare the next row from each input. If the rows are equal, we output a joined row and continue. If the rows are not equal, we discard the lesser of the two inputs and continue. Since the inputs are sorted, we know that we are discarding a row that is less than any of the remaining rows in either input and, thus, can never join.
The algorithm is motioned bellow.
get first row R1 from input 1
get first row R2 from input 2
WHILE not at the end of either input
BEGIN
IF R1 joins with R2
BEGIN
return (R1, R2)
get next row R2 from input 2
NED
ELSE IF R1 < R2
get next row R1 from input 1
ELSE
get next row R2 from input 2
END
Example:
SELECT a.*, b.*
FROM Table1 a
INNER MERGE JOIN Table2 b ON a.roll=b.roll
WHERE a.roll<=12
REMOTE Joint Hint:
It is useful when the join left table is a local table and the right table is a remote table. It is used when the left table is fewer rows then the right table.
REMOTE is only used with Inner join.
Example:
SELECT a.*, b.*
FROM Table1 a
INNER REMOTE JOIN Table2 b ON a.roll=b.roll
WHERE a.roll<=12
The link is mentioned bellow.
http://www.kunal-chowdhury.com/2012/01/join-hint-in-sql-guest-post-by-joydeep.html
http://sqlhints.com/2014/02/01/joins-in-sql-server/
http://www.interviewquestionspdf.com/2014/07/complex-joins-sql-joins-queries.html
http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx
http://www.w3resource.com/sql/joins/cross-join.php
http://www.c-sharpcorner.com/UploadFile/63f5c2/joins-in-sql-server/
http://www.madeiradata.com/loop-hash-and-merge-join-types/
http://www.mssqlcity.com/Articles/General/hashjoins.htm
