JOIN Tables Without Foreign Key In SQL Server

In this blog, we will understand how to join two or more SQL tables without using a foreign key. We will also get an overview of Join and Keys in SQL.

Introduction

In this blog, we will understand what a SQL Join is and how to join two or more SQL tables without using a foreign key. We will look into the various types of join as well.

Step 1

Create three SQL tables in SQL Server 2014 or higher. I have created 3 tables as mentioned below with their respective code.

Table-1 Employee

CREATE TABLE [dbo].[Employee](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Gender] [char](10) NULL,
    [Position] [nvarchar](50) NULL,
    [Salary] [int] NULL,
    [Department_Id] [int] NULL,
    [Incentive_Id] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Table-2 Department

CREATE TABLE [dbo].[Department](
    [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
    [DepartmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Table-3 Incentive

CREATE TABLE [dbo].[Incentive](
    [IncentiveId] [int] IDENTITY(1,1) NOT NULL,
    [IncentiveAmount] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [IncentiveId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Step 2 - Insert some demo data to all three tables

Insert records into the employee table.

/*INESRT VALUES IN EMPLOYEE TABLE*/
insert into Employee values('Anisha Agarwal','Female','Sales Excutive',30000,6,3)
insert into Employee values('Manish Agarwal','Male','Accountant',40000,1,6)
insert into Employee values('Fayaz Ansari','Male','UI Developer',50000,3,8)
insert into Employee values('Rahul Sharma','Male','Software Engineer',45000,3,8)
insert into Employee values('Abdul Rahim','Male','HR',30000,3,5)
insert into Employee values('Arvind Kumar','Male','HR',32000,3,5)
insert into Employee values('Priya Jain','Female','Marketing',25000,4,4)
insert into Employee values('Zoya','Female','Sales Excutive',30000,6,3)
insert into Employee values('Monika Agarwal','Female','Marketing',25000,4,4)
insert into Employee values('Suresh Kumar','Male','Assistant',20000,null,4)

Insert records into the department table.

/*INESRT VALUES IN DEPARTMENT TABLE*/
insert into Department values('Accountant')
insert into Department values('HR')
insert into Department values('IT')
insert into Department values('Markeing')
insert into Department values('Payroll')
insert into Department values('Sales')

Insert records into the incentive table.

/*INESRT VALUES IN INCENTIVE TABLE*/
insert into Incentive values(1000)
insert into Incentive values(2000)
insert into Incentive values(3000)
insert into Incentive values(4000)
insert into Incentive values(5000)
insert into Incentive values(6000)
insert into Incentive values(7000)
insert into Incentive values(8000)
insert into Incentive values(9000)
insert into Incentive values(10000)

Join

A Join clause is used for combining two or more tables in the SQL Server database based on their relative column or relationship with the primary and the foreign key. It gives us the desired output.

Types of joins in SQL server?

There are 5 major types of joins in SQL.

1. Inner join or simple join
2. Right join (right outer join)
3. Left join (left outer join)
4. Full join (full outer join)
5. Cross join

INNER JOIN

It is also known as SIMPLE JOIN which returns all the rows from both the joined tables when it has at least one column matched.

Syntax

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON column_name1=column_name2

/*INNER JOIN*/
select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
from Employee
INNER JOIN Department
on Employee.Department_Id=Department.DepartmentId
INNER JOIN  Incentive
on Employee.Incentive_Id=Incentive.IncentiveId

  

RIGHT JOIN (RIGHT OUTER JOIN)

This joins returns all rows from the right table and its matched rows from the left table.

 

Syntax

 

SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2

ON column_name1=column_name2

/*RIGHT JOIN*/
select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
from Employee
RIGHT JOIN Department
on Employee.Department_Id=Department.DepartmentId
RIGHT JOIN Incentive
on Employee.Incentive_Id=Incentive.IncentiveId

LEFT JOIN (LEFT OUTER JOIN)

This join returns all rows from the left table and its matched rows from a right table.

Syntax

SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON column_name1=column_name2

/*LEFT JOIN*/
select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
from Employee
LEFT JOIN Department
on Employee.Department_Id=Department.DepartmentId
LEFT JOIN Incentive
on Employee.Incentive_Id=Incentive.IncentiveId

 

FULL JOIN (FULL OUTER JOIN)

This joins returns all when there is a match either in the right or in the left table.

Syntax

SELECT column_name(s) FROM table_name1 FULL OUTER JOIN table_name2 ON column_name1=column_name2

/*FULL OUTER JOIN*/
select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
from Employee
FULL OUTER JOIN Department
on Employee.Department_Id=Department.DepartmentId
FULL OUTER JOIN Incentive
on Employee.Incentive_Id=Incentive.IncentiveId

CROSS JOIN

Returns the Cartesian product of the sets of records from the two or more joined tables.

Syntax

SELECT column_name(s) FROM table_name1 CROSS JOIN table_name2

/*FULL OUTER JOIN*/
select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
from Employee
CROSS JOIN Department
CROSS JOIN Incentive

All three tables will produce 600 records when we cross join.