SQL Join

In this article I will explain about joins in SQL server, join means combine/merge tow or more different table in SQL server based on certain join/filter condition. Join applied on two or more table when logical relation exists between SQL server tables. Logical relation means SQL server tables containing Primary and foreign key relation.

Join can also be applied on tables which may not be in primary and foreign key relation but data in both tables are more of a like parent-child (primary – foreign key relation). Lets understand with simple example.

Types of Join
1. Inner Join
2. Left Join
3. Right Join
4. Full Join
5. Cross Join


Before looking into types on join, lets create required tables and insert data.

CREATE TABLE [dbo].[tblDepartment]
(
[Id] INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(50) NULL
)


CREATE TABLE [dbo].[tblEmployee]
(
[Id] INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(50) NULL,
[DepartmentId] INT NULL
)


I have successfully created tblDepartment and tblEmployee. As discussed, above two tables not following parent-child relationship (primary and foreign key). Let’s insert some dummy data shown below.

Insert into tblDepartment values (1, 'IT')
Insert into tblDepartment values (2, 'Sales')
Insert into tblDepartment values (3, 'HR')
Insert into tblDepartment values (4, 'Finance')
Insert into tblDepartment (Id, Name) values (5, 'Accounts')


Insert into tblEmployee values (1, 'Salim', 4)
Insert into tblEmployee values (2, 'Rohit', 1)
Insert into tblEmployee values (3, 'John', 3)
Insert into tblEmployee values (4, 'Sameer', 2)
Insert into tblEmployee values (5, 'Saddam', 2)
Insert into tblEmployee values (6, 'Sandeep', 3)
Insert into tblEmployee values (7, 'Roma', 1)
Insert into tblEmployee values (8, 'Puja', 4)
Insert into tblEmployee (Id, Name) values (9, 'Ramesh')


Inner Join : It return all the records from both table which satisfy Join condition or common data from both the table based on join condition.

The above query will return common data from both the tables. If you notice I have inserted 9 rows in employee table but only 8 rows showing which are common from both the table and matching join condition. In Department table I have inserted 5 rows out of only 4 rows which are common shown in query result.

Left Join : This join returns all data from left table and common/matching data from both tables.

As shown in above figure, left join returns all data from left table (tblEmployee) and common data from both the tables.

Right Join : This join returns data from right table and common table from both the tables.

As shown in above figure, left join returns all data from right table (tblDepartment) and common data from both the tables.

Full Join : This type returns common data from both tables and also returns remaining data from both left and right tables.

Cross Join : This join is also called as Cartesian join since it returns the Cartesian product of the sets of rows from the joined tables. It means each row from table one combines with each row of the table two.
Number of rows return in cross join is multiplication of number of rows from table one and table two.

Number of rows from table one * Number of rows from table two = Cross Join [ 9 * 5 = 45 ]