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

  1. CREATE TABLE [dbo].[Employee](
  2. [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
  3. [Name] [nvarchar](50) NULL,
  4. [Gender] [char](10) NULL,
  5. [Position] [nvarchar](50) NULL,
  6. [Salary] [int] NULL,
  7. [Department_Id] [int] NULL,
  8. [Incentive_Id] [int] NULL,
  9. PRIMARY KEY CLUSTERED
  10. (
  11. [EmployeeId] ASC
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  13. ) ON [PRIMARY]
  15. GO

Table-2 Department

  1. CREATE TABLE [dbo].[Department](
  2. [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
  3. [DepartmentName] [nvarchar](50) NULL,
  4. PRIMARY KEY CLUSTERED
  5. (
  6. [DepartmentId] ASC
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  8. ) ON [PRIMARY]
  10. GO

Table-3 Incentive

  1. CREATE TABLE [dbo].[Incentive](
  2. [IncentiveId] [int] IDENTITY(1,1) NOT NULL,
  3. [IncentiveAmount] [int] NULL,
  4. PRIMARY KEY CLUSTERED
  5. (
  6. [IncentiveId] ASC
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  8. ) ON [PRIMARY]
  10. GO

Step 2 - Insert some demo data to all three tables

Insert records into the employee table.

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

Insert records into the department table.

  1. /*INESRT VALUES IN DEPARTMENT TABLE*/
  2. insert into Department values('Accountant')
  3. insert into Department values('HR')
  4. insert into Department values('IT')
  5. insert into Department values('Markeing')
  6. insert into Department values('Payroll')
  7. insert into Department values('Sales')

Insert records into the incentive table.

  1. /*INESRT VALUES IN INCENTIVE TABLE*/
  2. insert into Incentive values(1000)
  3. insert into Incentive values(2000)
  4. insert into Incentive values(3000)
  5. insert into Incentive values(4000)
  6. insert into Incentive values(5000)
  7. insert into Incentive values(6000)
  8. insert into Incentive values(7000)
  9. insert into Incentive values(8000)
  10. insert into Incentive values(9000)
  11. 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
  1. /*INNER JOIN*/
  2. select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
  3. from Employee
  4. INNER JOIN Department
  5. on Employee.Department_Id=Department.DepartmentId
  6. INNER JOIN Incentive
  7. on Employee.Incentive_Id=Incentive.IncentiveId
JOIN Tables Without Foreign Key In SQL Server

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
  1. /*RIGHT JOIN*/
  2. select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
  3. from Employee
  4. RIGHT JOIN Department
  5. on Employee.Department_Id=Department.DepartmentId
  6. RIGHT JOIN Incentive
  7. on Employee.Incentive_Id=Incentive.IncentiveId
JOIN Tables Without Foreign Key In SQL Server

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
  1. /*LEFT JOIN*/
  2. select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
  3. from Employee
  4. LEFT JOIN Department
  5. on Employee.Department_Id=Department.DepartmentId
  6. LEFT JOIN Incentive
  7. on Employee.Incentive_Id=Incentive.IncentiveId
JOIN Tables Without Foreign Key In SQL Server

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
  1. /*FULL OUTER JOIN*/
  2. select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
  3. from Employee
  4. FULL OUTER JOIN Department
  5. on Employee.Department_Id=Department.DepartmentId
  6. FULL OUTER JOIN Incentive
  7. on Employee.Incentive_Id=Incentive.IncentiveId
JOIN Tables Without Foreign Key In SQL Server

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
  1. /*FULL OUTER JOIN*/
  2. select Name,Gender,Position,Salary,DepartmentName,IncentiveAmount
  3. from Employee
  4. CROSS JOIN Department
  5. CROSS JOIN Incentive

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