Sql Joins are used to query data from two or more tables, based on the relationship between columns in the tables. Join condition defines the way two tables are related in a query by:
- Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
- Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.
Different Types of SQL Joins
- JOIN: Return rows when there is at least one match in both tables
- LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
- RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
- FULL JOIN: Return rows when there is a match in one of the tables
Before we see the join example, first will create two tables. They are
- Company – This table will hold the company details like Company Name, Address and etc.,
- Orders – This table will hold the orders details.
Company Table
CREATE TABLE [dbo].[Company](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Orders Table
CREATE TABLE [dbo].[Orders](
[OId] [int] IDENTITY(1,1) NOT NULL,
[OrderNo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CompanyId] [int] NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
We will store the company details in the company table like company name and address. In the company table ID column is the identity column. And in the Orders table order information will be stored. In the order table CompanyId will have the value of the id column of the company table. Hence CompanyId is the foreign key.
Execute the following query to insert the rows in the company and orders table.
insert into company(CompanyName,Address,City) values('Company1','East Street','cbe')
insert into company(CompanyName,Address,City) values('Company2','Moody Street','cbe')
insert into company(CompanyName,Address,City) values('Company3','North Street','cbe')
insert into company(CompanyName,Address,City) values('Company4','West Street','cbe')
insert into company(CompanyName,Address,City) values('Company5','Dia Street','cbe')
insert into company(CompanyName,Address,City) values('Company6','Sam Street','cbe')
insert into company(CompanyName,Address,City) values('Company7','Cap Street','cbe')
insert into orders(OrderNo,CompanyId) values ('O1',1)
insert into orders(OrderNo,CompanyId) values ('O2',2)
insert into orders(OrderNo,CompanyId) values ('O3',2)
insert into orders(OrderNo,CompanyId) values ('O4',3)
insert into orders(OrderNo,CompanyId) values ('O5',1)
insert into orders(OrderNo,CompanyId) values ('O6',4)
insert into orders(OrderNo,CompanyId) values ('O7',5)
insert into orders(OrderNo,CompanyId) values ('O8',5)
Inner Join or Join
The Inner Join key word will return there is at least one match in both tables.
Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Or
SELECT column_name(s)
FROM table_name1
JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Examples
The following query will return the information from both the Company and Orders table
SELECT Company.CompanyName, Company.Address, Orders.OrderNo
FROM Company
INNER JOIN Orders
ON Company.Id=Orders.CompanyId
ORDER BY Company.CompanyName
Result
Records will be displayed from Company and Orders table if there is match in the Id. If there are rows in "Company" that do not have matches in "Orders", those rows will NOT be displayed. We will insert another one record in the Company table. The company Id’s 6 and 7 will not be included in the list. There reason is there are no orders for the both companies (Company6 and Company7). If you want to include those two companies in the list we can use LEFT Join
Left Join
Left join will return all the rows from the left table (Company) even through no records in the right table (Orders).
Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example
SELECT Company.CompanyName, Company.Address, Orders.OrderNo
FROM Company
LEFT JOIN Orders
ON Company.Id=Orders.CompanyId
ORDER BY Company.CompanyName
Result
In the Left Join example Company6 and Company7 details included even though there is no Company Id available in the orders table.
Right Join
It will return all the rows from the right table (Company) even if there are no rows in the left table ( Orders )
In the orders table we will not have the orders for Company6 and Company7. When you use the left join Company6 and Company7 will not be included in the list. But if we use right join then they will be included.
Note: In the previous example I placed Orders as a right table and Company as a left table. In this example I have changed the position.
Syntax
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example
SELECT Company.CompanyName, Company.Address, Orders.OrderNo
FROM Orders
RIGHT JOIN Company
ON Company.Id=Orders.CompanyId
ORDER BY Company.CompanyName
Result
Here Company6 and Company7 details displayed from the right side table Company.
Before we go to the next join full join please create the table Product.
CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](50) NOT NULL,
[OrderId] [int] NOT NULL
)
In the product table OrderId is the foreign key. Make sure to create the relationship between Product.OrderId and Orders.Id
Execute the query to insert the rows in the Product table
Insert into Product(ProductName,OrderId) values ('Product1',1)
Insert into Product(ProductName,OrderId) values ('Product2',4)
Insert into Product(ProductName,OrderId) values ('Product3',2)
Insert into Product(ProductName,OrderId) values ('Product4',2)
Insert into Product(ProductName,OrderId) values ('Product5',1)
Full Join
Full Join will return all the rows from the left table Product and all the rows from the right table Orders. If there are rows in Orders that do not have matches in Product, or if there are rows in Product that do not have matches in Orders, those rows will be listed as well.
Syntax
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example
SELECT Product.ProductName,Product.OrderId,Orders.OrderNo
FROM Orders
FULL JOIN Product
ON Product.OrderId=Orders.OId
Result
In the product table we are not having product names for some orders. For that case, Null values is included in the productname and ordered columns. As a result, all of the rows from the Orders table, and all the rows from product table displayed.