SQL Server Joins

All the information collected through applications will be stored in tables in databases. Every application might have more than one table that holds data. This kind of data among various tables have relations between them. So, when users need to access complete data, they have to combine it from various tables. This is where joins are helpful. SQL Server provides several joins. In this post, you can get to know about all the joins of SQL Server. We will give you a detailed understanding of the joins with examples. Let us get started.

SQL Server Joins - Table of Content

What is an SQL Server join?

The data in SQL Server is distributed among multiple logical tables.When users need a complete meaningful set of data, they can use joins to query multiple tables. To apply joins between two tables, they should have a logical relationship between them, which means both the tables should have a similar column.

When applying a join condition, we have to specify the column from both the tables on which the join should base on.This column is typically a foreign key of a secondary table associated with the primary table. We have to use FROM clause in the SQL query to define a join. SQL Server also provides WHERE and HAVING clauses that we can use as search conditions to control the rows that have to be selected from the tables.

The syntax to define a join in SQL query is,

FROM first_table join_type second_table [ON (join_condition)]

first_table is the name of the first table.
second_table is the name of the second table.
join_type is the type of join.

Types of SQL Server joins

The type of join indicates how rows from one table should be combined with the rows of another table. It defines the way that the two tables are related. These are the various kinds of joins that the SQL Server provides.

  • Inner join
  • Left outer join
  • Right outer join
  • Full outer join
  • Self-join
  • Cross join

We will describe in detail all the joins in the later section of this post.

Example Tables

Let us take a retail scenario where all the customers’ details are stored in a ‘Customers’ table and the associated sales details are stored in a ‘Sales’ table.

Customers table

Create a table for customers using the below query.

CREATE table customers(
ID int PRIMARY KEY,
FullName VARCHAR(100) NOT NULL,
Age int NOT NULL,
Gender VARCHAR(10) NOT NULL,
Email VARCHAR(100) NOT NULL
);

Enter data into the table using the INSERT query.

INSERT into customers values(201, 'John Smith', 34, 'Male', 'john@gmail.com')

Use the same INSERT query with different values to insert the rest of the rows to the customers’ table. To view the data that you just entered into the customers’ table, execute the below query.

SELECT * from customers;

You will get the below table as a result.

ID FullName Age Gender Email
201 John Smith 34 Male john@gmail.com
202 Hannah Marin 16 Female hannah@gmail.com
203 Jane Doe 25 Female jane@gmail.com
204 Taylor Hastings 29 Male taylor@gmail.com
205 Alison parker 31 Female alison@gmail.com

Sales table

Create a sales table with the below query.

CREATE table sales(
OrderID int PRIMARY KEY,
CustomerID int NOT NULL,
Category VARCHAR(100) NOT NULL,
OrderedDate Date NOT NULL,
Amount int NOT NULL
);

Enter sales details into the table using the INSERT query.

INSERT into sales values(1241, 204, 'Electronics', '02/08/2020');

Use the same INSERT query with different values to insert the rest of the rows to the sales table. To view the data that you just entered into the sales table, execute the below query.

OrderID CustomerID Category OrderedDate Amount
1241 204 Electronics 02/08/2020 3500
1242 202 Accessories 07/08/2020 1605
1243 203 Clothing 13/08/2020 2800
1244 201 Footwear 18/08/2020 1200
1245 203 Accessories 22/08/2020 2200
1246 201 Clothing 28/08/2020 4600
1246 202 Electronics 01/09/2020 6730
1247 206 Clothing 04/09/2020 825

Inner join

The inner join is the most used join by developers. It is also known as EQUI join. It combines all the rows of two tables based on a common predicate. Here is the Venn diagram that depicts the inner join between two tables.

IMAGE

Let us apply an inner join on our example tables. Here is the SQL query for it.

SELECT customers.ID, customers.FullName, sales.OrderID, sales.Category, sales.OrderedDate
FROM customers
INNER JOIN sales
ON customers.ID = sales.CustomerID;

Here is the result of the above query.

ID FullName OrderID Category OrderedDate
204 Taylor Hastings 1241 Electronics 02/08/2020
202 Hannah Marin 1242 Accessories 07/08/2020
203 Jane Doe 1243 Clothing 13/08/2020
201 John Smith 1244 Footwear 18/08/2020
203 Jane Doe 1245 Accessories 22/08/2020
201 John Smith 1246 Clothing 28/08/2020
202 Hannah Marin 1247 Electronics 01/09/2020

SQL Server Certification Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

Left outer join

The left outer join combines all the rows of the left table and only the rows that meet the join predicate of the right table. Here is the Venn diagram that depicts the left outer join between two tables.

IMAGE

Here is the SQL query for the left outer join on the customers and sales tables.

SELECT customers.ID, customers.FullName, sales.OrderID, sales.Category, sales.OrderedDate
From customers
LEFT OUTER JOIN sales
ON customers.ID = sales.CustomerID;

Here is the result of the above query.

ID FullName OrderID Category OrderedDate
201 Taylor Hastings 1244 Footwear 18/08/2020
201 Hannah Marin 1246 Clothing 28/08/2020
202 Jane Doe 1242 Accessories 07/08/2020
202 John Smith 1247 Electronics 01/09/2020
203 Jane Doe 1243 Clothing 13/08/2020
203 John Smith 1245 Accessories 22/08/2020
204 Hannah Marin 1241 Electronics 02/08/2020
205 Alison parker null null null

Right outer join

The right outer join combines all the rows of the right table and only the rows that meet the join predicate of the left table. Here is the Venn diagram that depicts the right outer join between two tables.

IMAGE

Here is the SQL query for the right outer join on the customers and sales tables.

SELECT customers.ID, customers.FullName, sales.OrderID, sales.Category, sales.OrderedDate
From customers
RIGHT OUTER JOIN sales
ON customers.ID = sales.CustomerID;

Here is the result of the above query.

ID FullName OrderID Category OrderedDate
204 Taylor Hastings 1241 Electronics 02/08/2020
202 Hannah Marin 1242 Accessories 07/08/2020
203 Jane Doe 1243 Clothing 13/08/2020
201 John Smith 1244 Footwear 18/08/2020
203 Jane Doe 1245 Accessories 22/08/2020
201 John Smith 1246 Clothing 28/08/2020
202 Hannah Marin 1247 Electronics 01/09/2020
null null 1248 Clothing 04/09/2020

Full outer join

It returns the values of all the rows of the left table and the right table. It places null where the join predicate is not met. Below is the Venn diagram that depicts the full outer join between two tables.

IMAGE

Execute the below SQL query to apply full outer join on the customers and sales tables.

SELECT customers.ID, customers.FullName, sales.OrderID, sales.Category, sales.OrderedDate

From customers

FULL OUTER JOIN sales

ON customers.ID = sales.CustomerID;

Here is the result of the above query.

ID FullName OrderID Category OrderedDate
201 John Smith 1244 Footwear 18/08/2020
201 John Smith 1246 Clothing 28/08/2020
202 Hannah Marin 1242 Accessories 7/08/2020
202 Hannah Marin 1247 Electronics 01/09/2020
203 Jane Doe 1243 Clothing 13/08/2020
203 Jane Doe 1245 Accessories 22/08/2020
204 Taylor Hastings 1241 Electronics 2/08/2020
205 Alison parker null null null
null null 1248 Clothing 04/09/2020

Self join

A self join allows a table to join with itself, which has a unary relationship. Let’s apply a self join on the customers table. Here is the Venn diagram for self-join.

Execute the below query to see how it works.

SELECT A.ID, A.FullName AS CustomerName1, B.FullName AS CustomerName2, A.Gender
FROM Customer A, Customer B
WHERE A.Gender = B.Gender;

The result will be as follows.

ID CustomerName1 CustomerName2 Gender
201 John Smith John Smith Male
201 John Smith Taylor Hastings Male
202 Hannah Marin Alison parker Female
202 Hannah Marin Hannah Marin Female
202 Hannah Marin Jane Doe Female
203 Jane Doe Alison parker Female
203 Jane Doe Hannah Marin Female
203 Jane Doe Jane Doe Female
204 Taylor Hastings John Smith Male
204 Taylor Hastings Taylor Hastings Male
205 Alison parker Alison parker Female
205 Alison parker Hannah Marin Female
205 Alison parker Jane Doe Female

Cross join

The cross join is used to create a combination of every row from two tables. It returns the Cartesian product of rows from both tables. It does not establish a relationship between the two tables. Below is the Venn diagram that depicts the cross join between two tables.

IMAGE

Subscribe to our youtube channel to get new updates..!

Execute the below SQL query to apply a cross join on customers and sales tables.

SELECT customers.ID, customers.FullName, sales.OrderID, sales.Category, sales.OrderedDate
From customers
CROSS JOIN sales;

Here is the partial set of the result set.

ID FullName OrderID Category OrderedDate
201 John Smith 1241 Electronics 02/08/2020
201 John Smith 1242 Accessories 07/08/2020
201 John Smith 1243 Clothing 13/08/2020
201 John Smith 1244 Footwear 18/08/2020
201 John Smith 1245 Accessories 22/08/2020
201 John Smith 1246 Clothing 28/08/2020
201 John Smith 1247 Electronics 01/09/2020
201 John Smith 1248 Clothing 04/09/2020
202 Hannah Marin 1241 Electronics 02/08/2020
202 Hannah Marin 1242 Accessories 7/08/2020
202 Hannah Marin 1243 Clothing 13/08/2020
202 Hannah Marin 1244 Footwear 18/08/2020
202 Hannah Marin 1245 Accessories 22/08/2020
202 Hannah Marin 1246 Clothing 28/08/2020
202 Hannah Marin 1247 Electronics 01/09/2020
202 Hannah Marin 1248 Clothing 04/09/2020
203 Jane Doe 1241 Electronics 02/08/2020
203 Jane Doe 1242 Accessories 07/08/2020
203 Jane Doe 1243 Clothing 13/08/2020

SQL Server Certification Training

Weekday / Weekend Batches

Conclusion

SQL Server provides various types of joins that the developers can use. All you have to do is select the one that fits your needs and start implementing it. Create some more tables and try applying joins on two or more tables. If you do not have a SQL Server installed on your system, you can use an online SQL editor to try out the joins.

Categories

SAP

Request for more information

Webinar

Register free live webinar on Linux Shell Scripting

30th October | 07:00 pm

0 Registered

Mudassir
Mudassir
DevOps ERP and IAM tools
Mudaasir is a programming developer for hkr trainings. He has a well knowledge of today’s technology and I’ve loved technology my entire life. And also been lucky enough to work for the programmer including science and technology. Big thanks to everyone who has followed me on LinkedIn and twitter.

WhatsApp
To Top