SQL Server Joins

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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. Isolation Levels in SQL Server

2. Exception Handling in SQL Server

3. SQL Server Data Tools