M. FAISOL RIZA

In this post, we will talk about Join clause in SQL Server. SQL joins are used to query data from two or more tables, based on a relationship between certain columns in those tables.

There are few kinds of join, they are : INER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. We will talk about those join respectively.

But before we begin, let’s create the table we will use for the example. Let’s create table “Persons” and “Orders”.

Table “Persons”

CREATE TABLE Persons
(
P_Id INT NOT NULL PRIMARY KEY,
Lastname VARCHAR(25),
FirstName VARCHAR(25),
Address VARCHAR(30),
City VARCHAR(20)
)
GO

INSERT INTO Persons (P_Id, Lastname, FirstName, Address, City) VALUES (1, 'Hansen', 'Ola', 'Timoteivn 10', 'Sandness')
INSERT INTO Persons (P_Id, Lastname, FirstName, Address, City) VALUES (2, 'Svendson', 'Tove', 'Borgvn 23', 'Sandness')
INSERT INTO Persons (P_Id, Lastname, FirstName, Address, City) VALUES (3, 'Pettersen', 'Kari', 'Storgt 20', 'Stavanger')

SELECT * FROM Persons

Table “Orders”

CREATE TABLE Orders
(
O_Id INT NOT NULL PRIMARY KEY,
OrderNo VARCHAR(8),
P_Id INT
)

INSERT INTO MvcLabs.dbo.Orders (O_Id, OrderNo, P_Id) VALUES (1, '77895', 3)
INSERT INTO MvcLabs.dbo.Orders (O_Id, OrderNo, P_Id) VALUES (2, '44678', 3)
INSERT INTO MvcLabs.dbo.Orders (O_Id, OrderNo, P_Id) VALUES (3, '22456', 1)
INSERT INTO MvcLabs.dbo.Orders (O_Id, OrderNo, P_Id) VALUES (4, '24562', 1)
INSERT INTO MvcLabs.dbo.Orders (O_Id, OrderNo, P_Id) VALUES (5, '34764', 15)

GO

SELECT * FROM Orders

In order to avoid too long article, I will divide this article into few parts:

1. SQL server – INNER JOIN clause

2. SQL server – LEFT JOIN clause

3. SQL server – RIGHT JOIN clause

4. SQL server – FULL JOIN clause

So we can get more focus on each topic of our work. Check it out.