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.
Agenda
- Creating two sample tables for Join
- Insert data into sample Join tables
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Cross Join
For demonstration and understanding purposes of SQL Server Join operations I will create two tables, J1 and J2, then I will insert some sample data into these tables.
Create a database Table J1 as in the following:
Create Table J1
(
EmpId INT primary key,
EmpName CHAR
)
Insert the following data into Table J1 as in the following:
Insert into J1
Values (2,'b')
Insert into J1
Values (3,'c')
Insert into J1
Values (4,'d')
Insert into J1
Values (5,'e')
![]()
Create a database Table J2 as in the following:
Create Table J2
(
EmpId INT,
Department VARCHAR(20)
)
Insert the following data into Table J2 as in the following:
Insert into J2
Values(1,'IT')
EmpId Department
1 IT
2 Marketing
4 Multimedia
6 Psychology
7 IT
Insert into J2
Values (2,'Marketing')
![]()
Insert into J2 as in the following:
Values (4,'Multimedia')
Insert into J2
Values (6,'Psychology')
Insert into J2
Values (7,'IT')
Inner Join
In the following , only matching data will come from both tables.
Select J1.EmpId,J2.Department
From J1 Inner Join J2
On J1.EmpId = J2.EmpId
From both the tables, J1 and J2, only the EmpIds 1, 2 and 4 match, hence only those (matching) records will be displayed.
![]()
Left Outer Join
All rows will be listed from the Left table and the matching data from the Right table, showing NULL for data that does not match.
Select J1.EmpId,J2.Department
From J1 Left Outer Join J2
On J1.EmpId = J2.EmpId
![]()
As you can observe, all the data is listed from the selected column, EmpId, of the Left table J1 whereas the right side table shows the matching and Null for the missing data for the respective Left column.
Right Outer Join
All rows will be listed from the Right table and the matching data from the Left table showing NULL for the data that is not matched.
Select J1.EmpId,J2.Department
From J1 Right Outer Join J2
On J1.EmpId = J2.EmpId
![]()
Full Outer Join
This join produces Matched and Unmatched data from both sides of the JOIN Operation tables. In other words a combination of Left and Right Outer Join.
Select J1.EmpId,J2.Department
From J1 Full Outer Join J2
On J1.EmpId = J2.EmpId
![]()
Cross Join or Cartesian Product
This join produces cross or Cartesian data of both sides of the table on the Join operation. It’s outcome is the Cross (X) product of rows from both sides of the tables. In our case we have 5 rows in each table and so a Cartesian product or Cross product will be 5 x 5 = 25 rows.
The purpose of the join is mainly to determine the performance of your normalized tables.
Select J1.EmpId,J2.Department
From J1 Cross Join J2
Or Cross Join can also be specified as:
Select J1.EmpId,J2.Department
From J1 , J2
![]()