SQL Joins: Optimizing SQL Queries for Efficiency

Introduction

In SQL Server, a join is a way to combine data from two or more tables based on a related column between them. The join operation allows you to retrieve data from multiple tables at once and create a new virtual table that contains information from all the tables involved in the join. This tutorial explains different types of joins and how to use joins in SQL to get data from multiple tables.

Types of Joins in SQL Server

There are different types of joins in SQL Server, including:

Joins are a way of combining information from multiple tables to form result tables. The most common types of Joins in SQL Server are the INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN.

There are four types of joins in SQL Server.

INNER JOIN - Returns only the matching rows from both tables.
OUTER JOIN
- Left Outer Join - Returns all the rows from the left table and matching rows from the right table.
- Right Outer Join - Returns all the rows from the right table and matching rows from the left table.
- Full Outer Join - Returns all the rows from both tables and if there is no match, then it returns NULL values.
CROSS JOIN - Returns the Cartesian product of both tables, which means it returns all possible combinations of rows between the two tables.
SELF JOIN

To perform a join in SQL Server, you need to specify the tables to join, the join type, and the join condition using the JOIN keyword. Here's an example of an inner join:

SELECT *
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2;

This query joins table1 and table2 on the column1 and column2 columns, respectively, and returns all the matching rows from both tables.

INNER JOIN in SQL Server

An INNER JOIN matches rows in one table with rows in another based on the join condition. The result set will contain the matched rows from both tables.

OUTER JOIN in SQL Server

OUTER JOIN is a default join type in SQL Server and is also called an anti-join or cross product because it creates a Cartesian product of the tables being joined and then filters out duplicates based on the ON clause condition.

CROSS JOIN in SQL Server

A CROSS JOIN returns all rows from both tables and creates a result set that is a Cartesian product (cross product) of two or more tables. For example, if you want to create the cross-product of your two tables, AdventureWorksDW2017.Production.Product and AdventureWorksDW2017.Production.ProductModel, you would write:

SELECT p1.* FROM Production.Product AS p1 CROSS JOIN Production.ProductModel AS p2

The result set contains every row in both tables because no WHERE clause has been specified to limit it to one or the other table; each row appears once in the result set with its corresponding values from both columns of each table (eid and ProductModelID).

SELF JOIN in SQL Server

SELF Join is a join where a table is joined with itself. It is used for comparing values in a column with other values in the same column in the same table. The self-join is an inner join because it compares rows within the same table.

When writing SQL queries, you can use different types of joins depending on your needs. Joins in SQL Server include inner joins, outer joins, cross joins, and self-joins.

SQL Joins with Examples

Let's understand these Joins in detail with examples.

To understand joins, let's create two tables named "tblEmployee" and "tblDepartment" with some data, as shown below.

tblEmployee

tblDepartment

Now both tables are ready with their data. In tblEmployee, some DepartmentIds are not assigned to any employee. Hence their DepartmentId is showing as NULL.

So in total, we've 25 Employees and 9 Departments. Now let's understand joins.

Inner Join Example

An Inner join requires matching records from both tables. You can join your tables using the "JOIN" or "INNER JOIN" keyword. The "INNER" keyword is optional.

SELECT <columns> from TABLE_A a  
JOIN TABLE_B b  
ON a.KEY = b.KEY;

SELECT e.FirstName+' '+ e.LastName as [Full Name], e.Salary, d.DeptName, d.Location FROM tblEmployee e  
INNER JOIN tblDepartment d  
ON e.DepartmentId = d.DeptId;

Left Outer Join

You can use the "LEFT OUTER JOIN" keywords to do Left Joining.

A left outer join returns a result containing matching and non-matching rows from the left table. In other words, it returns all rows from the left table. If the join condition matches 0 records in Table B, the join will still return a row in the Result Set, but with NULL in each column from Table B.

SELECT <columns> from TABLE_A a  
LEFT OUTER JOIN TABLE_B b  
ON a.KEY = b.KEY;

SELECT e.FirstName+' '+ e.LastName as [Full Name], e.Salary, d.DeptName, d.Location FROM tblEmployee e  
LEFT OUTER JOIN tblDepartment d  
ON e.DepartmentId = d.DeptId;

As you can see in the preceding output, non-matching rows are represented as NULL in both columns.

But what if my manager says, instead of this NULL, some information should be shown, like, No Department or Location or something? Then what should we do?

So in such a scenario, we'll use the "ISNULL" method; this method will allow us to show the data we need to display instead of NULL.

SELECT e.FirstName+' '+ e.LastName as [Full Name], e.Salary, ISNULL(d.DeptName,'General') as Department, ISNULL(d.Location,'No Location') as Location FROM tblEmployee e  
LEFT OUTER JOIN tblDepartment d  
ON e.DepartmentId = d.DeptId

As you can see, this data looks much better than the previous one. Instead of NULL, it says that these many employees belong to the "GENERAL Department" and their "Location is not yet specified."

Right Outer Join

"RIGHT OUTER JOIN" keywords are used to do Right Joining.

A right outer join is nearly the same as a left outer join. A right outer join retrieves matching data from both of the tables + non-matching data from Table B.

SELECT <columns> from TABLE_A a  
RIGHT OUTER JOIN TABLE_B b  
ON a.KEY = b.KEY;

SELECT e.FirstName+' '+ e.LastName as [Full Name], e.Salary, d.DeptName, d.Location FROM tblEmployee e  
RIGHT OUTER JOIN tblDepartment d  
ON e.DepartmentId = d.DeptId;

NULL will appear in columns from Table A for those records that have no match in Table B. You can replace the NULL value with whatever value you want using the ISNULL() method, as shown for Left Outer Join.

Full Outer Join

You can use the "FULL OUTER JOIN" keywords to do a Full outer join.

In a full outer join, the result set will display all the data from Table A and Table B. The unmatched data has NULL for every non-matching row in a full outer join.

SELECT <columns> from TABLE_A a  
FULL OUTER JOIN TABLE_B b  
ON a.KEY = b.KEY;

SELECT e.FirstName+' '+ e.LastName as [Full Name], e.Salary, d.DeptName, d.Location FROM tblEmployee e  
FULL OUTER JOIN tblDepartment d  
ON e.DepartmentId = d.DeptId;

Cross Join

Use the "CROSS JOIN" keywords to do the cross-joining.

A cross-join returns the Cartesian product of rows from both tables in the join. In other words, it'll produce rows that combine rows from Table A with each row from Table B.

SELECT <columns> from TABLE_A a  
CROSS JOIN TABLE_B b

SELECT e.FirstName+' '+ e.LastName as [Full Name], e.Salary, d.DeptName, d.Location FROM tblEmployee e  
CROSS JOIN tblDepartment d

You'll see an output of "225" rows when you run the cross-join command.

25 Rows from tblEmployee

09 Rows from tblDepartment

Cross join => [25 X 09] = 225 Rows.

Self Join

A self join is a type of join in SQL Server where a table is joined to itself based on a common column. In other words, a self join is when you treat a single table as if it were two separate tables and join them together based on a common column.

Self joins are useful when you need to compare rows within the same table or when you need to find related records within the same table.

select e1.EmpId,e1.FirstName, e1.LastName, e1.Salary, e2.EmpId, e2.FirstName, e2.LastName,e2.Salary from tblEmployee e1  
JOIN tblEmployee e2  
on e1.FirstName = e2.FirstName  
ORDER by e1.FirstName;

If you see the output, the tblEmployee table is joined with itself by FirstName.

Some names like Jyoti, Rahul, and so on are repeated four times because there're two people with the same FirstName. If we self-join the FirstName of one table with the same FirstName of another table, it'll produce four outputs, as represented below.

First, "Jyoti Kakawat" will make a pair with herself (1 row), then she'll make a pair with "Jyoti Mishra" (2 rows). Then, "Jyoti Mishra" will make a pair with herself (3 rows), and then she'll also make another pair with "Jyoti Kakawat" (4 rows).

SQL Joins Performance Optimization Tips

SQL joins are one of the most effective and flexible tools that SQL developers have at their disposal. They allow you to join tables, creating all kinds of data sets from just two (or more) tables. But with great power comes great responsibility: if your joins aren't optimized, they can significantly slow down the performance of your SQL queries. Fortunately, there are many simple things you can do to improve the performance of your SQL Joins:

Use the correct type of joins

Now that you know how to optimize your SQL joins, I want to go over some specific join types:

Inner join. An inner join will return rows from the matching table in both tables. This is the default for all joins and is used if no other type is specified.

Left outer join. A left outer join will return all the rows from the first table with NULL values for any columns it doesn't match on in the second table (i.e., it's "unmatched").

Right outer join. A suitable outer join returns all the rows from both tables but only includes those where values exist (i.e., they're "matched"). For example, say we have two tables called employees and departments, which contain employee IDs and department names respectively: ```SQL SELECT e1.* FROM employees AS e1 LEFT OUTER JOIN departments AS d1 ON (e1.department_id = d1 .id) WHERE 1 = 2; ```

Specify columns

Specify columns that are related by a primary key or foreign key.

If you have a one-to-one relationship, use the primary and foreign keys to specify the join. If you have a one-to-many relationship, use the primary and foreign keys to specify joins.

Order tables in joins

Order tables in joins according to their size.

Use the right type of join: inner, outer (left or right), and full outer.

Specify columns related by a primary key or foreign key as part of the join conditions so that you can avoid adding extra unnecessary columns to your query plans if they're not needed.

Check which part of a join is actually needed to run—a lot of time, this step gets skipped, but it will make a huge difference!

Check which join actually needs to run

There are several SQL commands that you can use to check the execution plans of your queries and optimize them. The most popular ones are:

EXPLAIN (ANALYZE, VERBOSE)

EXPLAIN PLAN

EXPLAIN VERBOSELY

Use these commands to find out which join needs to run.

What we can do to improve your SQL Joins performance?

There are many things you can do to improve your SQL Joins performance. Here are some examples.

It is essential to choose the correct join operation for your queries. Use the correct type of join. Every kind of join has its own characteristics; some are better suited for certain types of data than others. For example, an inner join returns any rows that match in both tables, while an outer join will return all rows from one table (the left side) and only those rows that match from another table (the right side).

Specify columns related by a primary key or foreign key: If you have two tables with no natural relationship between them. Still, they share some common columns (such as employee name), then place these common fields in one of your joins when specifying which columns should be joined together during query execution. This will ensure optimal performance as each row only needs to be compared once instead of twice.

For example

SELECT c1 FROM t1a LEFT JOIN t2b ON c2 UNION SELECT c3 FROM t3c LEFT OUTER JOIN t4d ON c5;

Conclusion

Here you learned joins in SQL Server, their types, and how to use them in SQL.