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.

  To gain in-depth knowledge with practical experience in SQL server, then explore  SQL server Training!

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.

customers table

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.

sales table

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.

inner join

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.

inner join

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.

left outer join

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.

left outer join

 Explore Sql server sample resume ! Download & Edit, Get Noticed by Top Employers! 

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.

right outer join

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.

right outer join
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.

full outer join

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.

full outer join
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.

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.

self join
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.

cross join

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.

Related Articles:

1.SQL Server Editions

2. Data Modelling in SQL Server

3. Isolation Levels in SQL Server

4. Exception Handling in SQL Server

5. Sql server 2016 new features

6. SQL Server Data Tools

Categories

Request for more information

Gayathri
Gayathri
Research Analyst
As a senior Technical Content Writer for HKR Trainings, Gayathri has a good comprehension of the present technical innovations, which incorporates perspectives like Business Intelligence and Analytics. She conveys advanced technical ideas precisely and vividly, as conceivable to the target group, guaranteeing that the content is available to clients. She writes qualitative content in the field of Data Warehousing & ETL, Big Data Analytics, and ERP Tools. Connect me on LinkedIn.

WhatsApp
To Top