Must Know SQL Server JOINS For All .NET Developers


As a .NET Developer most of the common tasks you do are database related operations, like INSERT, SELECT UPDATE and DELETE. These tasks are often collectively referred to as CRUD operations. The problem comes when writing a complex query directly or in a Stored Procedure that retrieves expected data from more than one table of your Normalized database, in other words you are working on “Joining the Tables” to pull the data.

Article Covers

For demonstration and understanding purposes of SQL Server Join operations I will create two tables, Employee and Department, then I will insert some sample data into these tables.

Create a database Table Employee as in the following :

CREATE TABLE dbo.Employee
    (
    EmployeeId NUMERIC (18) NOT NULL,
    Name       NVARCHAR (50),
    CONSTRAINT PK_Employee PRIMARY KEY (EmployeeId)
    )
GO

Insert the following data into Table Employee as in the following :

INSERT INTO dbo.Employee (EmployeeId, Name)
VALUES (1, 'Employee 1')
GO

INSERT INTO dbo.Employee (EmployeeId, Name)
VALUES (2, 'Employee 2')
GO

INSERT INTO dbo.Employee (EmployeeId, Name)
VALUES (3, 'Employee 3')
GO

INSERT INTO dbo.Employee (EmployeeId, Name)
VALUES (4, 'Employee 4')
GO

INSERT INTO dbo.Employee (EmployeeId, Name)
VALUES (5, 'Employee 5')
GO

To view data of Employee Table :

Select * From Employee
EmployeeId Name
1 Employee 1
2 Emplyee 2
3 Employee 3
4 Employee 4
5 Employee 5

Create a database Table Department as in the following :

CREATE TABLE dbo.Department
    (
    EmployeeId NUMERIC (18),
    Department NVARCHAR (50)
    )
GO

Insert the following data into Table Department as in the following :

INSERT INTO dbo.Department (EmployeeId, Department)
VALUES (1, 'IT')
GO

INSERT INTO dbo.Department (EmployeeId, Department)
VALUES (2, 'MARKETING')
GO

INSERT INTO dbo.Department (EmployeeId, Department)
VALUES (4, 'TESTING')
GO

INSERT INTO dbo.Department (EmployeeId, Department)
VALUES (6, 'HR')
GO

INSERT INTO dbo.Department (EmployeeId, Department)
VALUES (7, 'IT')
GO

To view data of Department table :

Select * From Department
EmployeeId Department
1 IT
2 MARKETING
4 TESTING
6 HR
7 IT

Inner Join

img_innerjoinThe INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

Syntax : SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

Select Employee.EmployeeId, Department.Department
From Employee Inner Join Department
On Employee.EmployeeId = Department.EmployeeId

From both the tables, Employee and Department, only the EmployeeId 1, 2 and 4 match, hence only those (matching) records will be displayed.

EmployeeId Department
1 IT
2 MARKETING
4 TESTING

Left Outer Join / Left Join

img_leftjoinThe LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

Note : In some databases LEFT JOIN is called LEFT OUTER JOIN.

Syntax : SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

Select Employee.EmployeeId, Department.Department
From Employee Left Outer Join Department
On Employee.EmployeeId = Department.EmployeeId
EmployeeId Department
1 IT
2 MARKETING
3 NULL
4 TESTING
5 NULL

As you can observe, all the data is listed from the selected column, EmployeeId, of the Left table Employee whereas the right side table shows the matching and Null for the missing data for the respective Left column.

Right Outer Join

img_rightjoinThe RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

Note : In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Syntax : SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

Select Employee.EmployeeId, Department.Department
From Employee Right Outer Join Department
On Employee.EmployeeId = Department.EmployeeId
EmployeeId Department
1 IT
2 MARKETING
4 TESTING
NULL HR
NULL IT

Full Outer Join

img_fulljoinThe FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2) or The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Syntax : SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Select Employee.EmployeeId, Department.Department
From Employee Full Outer Join Department
On Employee.EmployeeId = Department.EmployeeId
EmployeeId Department
1 IT
2 MARKETING
3 NULL
4 TESTING
5 NULL
NULL HR
NULL IT

Cross Join

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

Syntax : SELECT * FROM table1 CROSS JOIN table2; OR SELECT * FROM table1, table2;

Select Employee.EmployeeId, Department.Department
From Employee Cross Join Department
EmployeeId Department
1 IT
2 IT
3 IT
4 IT
5 IT
1 MARKETING
2 MARKETING
3 MARKETING
4 MARKETING
5 MARKETING
1 TESTING
2 TESTING
3 TESTING
4 TESTING
5 TESTING
1 HR
2 HR
3 HR
4 HR
5 HR
1 IT
2 IT
3 IT
4 IT
5 IT
Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s