Types of Join in SQL Server

Introduction

If you’re new to SQL, joins can be a daunting concept. There are many different types of joins and seemingly little information available that explains and compares them. But after you digest a few basic concepts, the practice of joins actually isn’t very difficult.

Let’s look at the fundamentals that should give you a solid, functional knowledge of how to use the JOIN statement. We won’t consider some more advanced JOIN statement concepts, such as aliasing, join order, conceptual joins, and join hints. While the descriptions I’ll present in this article relate directly to Microsoft SQL Server, they also apply to any SQL-92 compliant database.

The JOIN concept
JOIN is a query clause that can be used with the SELECT, UPDATE, and DELETE data query statements to simultaneously affect rows from multiple tables. There are several distinct types of JOIN statements that return different data result sets.

Joined tables must each include at least one field in both tables that contain comparable data. For example, if you want to join a Customer table and a Transactiontable, they both must contain a common element, such as a CustomerID column, to serve as a key on which the data can be matched. Tables can be joined on multiple columns so long as the columns have the potential to supply matching information. Column names across tables don't have to be the same, although for readability this standard is generally preferred.

When you do use like column names in multiple tables, you must use fully qualified column names. This is a “dot” notation that combines the names of tables and columns. For example, if I have two tables, Customer and Transaction, and they both contain the column CustomerID, I’d use the dot notation, as in Customer.CustomerID andTransaction.CustomerID, to let the database know which column from which table I’m referring.

Now that we’ve examined the basic theory, let’s take a look at the various types of joins and examples of each.

The basic JOIN statement
A basic JOIN statement has the following format:
SELECT Customer.CustomerID, TransID, TransAmt
FROM Customer JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

In practice, you'd never use the example above because the type of join is not specified. In this case, SQL Server assumes an INNER JOIN. You can get the equivalent to this query by using the statement:
SELECT Customer.CustomerID, TransID, TransAmt
FROM Customer, Transaction;

However, the example is useful to point out a few noteworthy concepts:

• TransID and TransAmt do not require fully qualified names because they exist in only one of the tables. You can use fully qualified names for readability if you wish.
• The Customer table is considered to be the “left” table because it was called first. Likewise, the Transaction table is the “right” table.
• You can use more than two tables, in which case each one is “naturally” joined to the cumulative result in the order they are listed, unless controlled by other functionality such as “join hints” or parenthesis.
• You may use WHERE and ORDER BY clauses with any JOIN statement to limit the scope of your results. Note that these clauses are applied to the results of your JOIN statement.
• SQL Server does not recognize the semicolon (;), but I use it in the included examples to denote the end of each statement, as would be expected by most other RDBMSs.

The notorious CROSS JOIN
The CROSS JOIN has earned a bad reputation because it’s very resource intensive and returns results of questionable usefulness. When you use the CROSS JOIN, you're given a result set containing every possible combination of the rows returned from each table. Take the following example:
SELECT CustomerName, TransDate, TransAmt
FROM Customer CROSS JOIN Transaction;

With the CROSS JOIN, you aren’t actually free to limit the results, but you can use the ORDER BY clause to control the way they are returned. If the tables joined in this example contained only five rows each, you would get 25 rows of results. Every CustomerName would be listed as associated with every TransDate and TransAmt.

I really did try to come up with examples where this function was useful, and they were all very contrived. However, I’m sure someone out there is generating lists of all their products in all possible colors or something similar, or we wouldn’t have this wonderful but dangerous feature.

The INNER JOIN drops rows
When you perform an INNER JOIN, only rows that match up are returned. Any time a row from either table doesn’t have corresponding values from the other table, it is disregarded. Because stray rows aren’t included, you don’t have any of the “left” and “right” nonsense to deal with and the order in which you present tables matters only if you have more than two to compare. Since this is a simple concept, here’s a simple example:
SELECT CustomerName, TransDate
FROM Customer INNER JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

If a row in the Transaction table contains a CustomerID that’s not listed in the Customertable, that row will not be returned as part of the result set. Likewise, if the Customertable has a CustomerID with no corresponding rows in the Transaction table, the row from the Customer table won’t be returned.

The OUTER JOIN can include mismatched rows
OUTER JOINs, sometimes called “complex joins,” aren’t actually complicated. They are so-called because SQL Server performs two functions for each OUTER JOIN.

The first function performed is an INNER JOIN. The second function includes the rows that the INNER JOIN would have dropped. Which rows are included depends on the type of OUTER JOIN that is used and the order the tables were presented.

There are three types of an OUTER JOIN: LEFT, RIGHT, and FULL. As you’ve probably guessed, the LEFT OUTER JOIN keeps the stray rows from the “left” table (the one listed first in your query statement). In the result set, columns from the other table that have no corresponding data are filled with NULL values. Similarly, the RIGHT OUTER JOIN keeps stray rows from the right table, filling columns from the left table with NULL values. The FULL OUTER JOIN keeps all stray rows as part of the result set. Here is your example:
SELECT CustomerName, TransDate, TransAmt
FROM Customer LEFT OUTER JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

Customer names that have no associated transactions will still be displayed. However, transactions with no corresponding customers will not, because we used a LEFT OUTER JOIN and the Customer table was listed first.

In SQL Server, the word OUTER is actually optional. The clauses LEFT JOIN, RIGHT JOIN, and FULL JOIN are equivalent to LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN, respectively.

Another addition to your SQL toolbox
Although the JOIN statement is often perceived as a complicated concept, you can now see that it’s a powerful timesaving resource that’s relatively easy to understand. Use this functionality to get related information from multiple tables with a single query and to skillfully reference normalized data. Once you’ve mastered JOINs, you can elegantly maneuver within even the most complex database.

Using the Code

Join is very useful to fetching records from multiple tables with reference to common column between them.

To understand join with example, we have to create two tables in SQL Server database.

1. Employee
   Hide   Copy Code

   create table Employee(
    
   id int identity(1,1) primary key,
   Username varchar(50),
   FirstName varchar(50),
   LastName varchar(50),
   DepartID int
    
   ) 

2. Departments
Hide   Copy Code

create table Departments(
 
id int identity(1,1) primary key,
DepartmentName varchar(50)
 
) 

Now fill Employee table with demo records like that.

Fill Department table also like this....

1) Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join.

Hide   Copy Code

 select e1.Username,e1.FirstName,e1.LastName,e2.DepartmentName _
from Employee e1 inner join Departments e2 on e1.DepartID=e2.id 

It gives matched rows from both tables with reference to DepartID of first table and id of second table like this.

Equi-Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query forjoin using equality operator, then that join query comes under Equi join. 
Equi join has only (=) operator in join condition.
Equi join can be inner join, left outer join, right outer join.

Check the query for equi-join:

Hide   Copy Code

 SELECT * FROM Employee e1 JOIN Departments e2 ON e1.DepartID = e2.id 

2) Outer Join

Outer join returns all the rows of both tables whether it has matched or not.

We have three types of outer join:

1. Left outer join
2. Right outer join
3. Full outer join

a) Left Outer join

Left join displays all the rows from first table and matched rows from second table like that..

Hide   Copy Code

 SELECT * FROM Employee e1 LEFT OUTER JOIN Departments e2
ON e1.DepartID = e2.id 

Result:

b) Right outer join

Right outer join displays all the rows of second table and matched rows from first table like that.

Hide   Copy Code

 SELECT * FROM Employee e1 RIGHT OUTER JOIN Departments e2
ON e1.DepartID = e2.id

Result:

3) Full outer join

Full outer join returns all the rows from both tables whether it has been matched or not.

Hide   Copy Code

 SELECT * FROM Employee e1 FULL OUTER JOIN Departments e2
ON e1.DepartID = e2.id 

Result:

3) Cross Join

A cross join that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian product is the number of the rows in the first table multiplied by the number of rows in the second table like this.

Hide   Copy Code

 SELECT * FROM Employee cross join Departments e2 

You can write a query like this also:

Hide   Copy Code

 SELECT * FROM Employee , Departments e2

 4) Self Join

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here, we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

Hide   Copy Code

SELECT e1.Username,e1.FirstName,e1.LastName from Employee e1 _
inner join Employee e2 on e1.id=e2.DepartID

Here, I have retrieved data in which id and DepartID of employee table has been matched: