Joins in SQL Server

Joins in SQL Server are used to retrieve data from two or more tables with a joining condition.

There're 4 types of Join in SQL Server, they are:

1. Inner Join
2. Outer Join
   1. Left Outer Join
   2. Right Outer Join
   3. Full Outer Join
3. Cross Join
4. Self Join

Let's understand these Joins in details with examples.

To understand joins, let's create 2 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

An Inner join requires matching records from both tables. You can use the “JOIN” or “INNER JOIN” keyword to join you tables. The “INNER” keyword is optional.

Command

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

Graphical Representation



Example

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 “LEFT OUTER JOIN” keywords to do Left Joining.

A left outer join returns a result containing both 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 then the join will still return a row in the Result Set, but with NULL in each column from Table B.

Command

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

Graphical Representation



Example

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 the columns.

But what if my manager says, instead if this NULL, some informative should be shown like, No Department or NO Location or something, then what should we do?

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

Example (Modified)

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

Now as you can see, this data is looking much better than the previous one. Instead of NULL, it says that these many employees belong to “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.

Command

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

Graphical Representation



Example

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

To do a Full outer join you can use the “FULL OUTER JOIN” keywords.

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

Command

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

Graphical Representation



Example

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 “CROSS JOIN” keywords to do 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 combines rows from Table A with each row from Table B.

Command

SELECT <columns> from TABLE_A a
CROSS JOIN TABLE_B b

Graphical Representation



Example

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

When you run the cross join command, you'll see output of “225” rows.

25 Rows from tblEmployee
09 Rows from tblDepartment
Cross Join => [25 X 09] = 225 Rows.

Self Join

A self-join is the joining of the table to itself.

Example

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, tblEmployee table is joined with itself by FirstName.

Some of the names like Jyoti, Rahul and so on are repeated 4 times, because there're 2 people with the same FirstName and if we self-join the FirstName of one table with the same FirstName of another table then it'll produce 4 output 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).

Conclusion

I hope this article helps you to understand the concept of Joins in SQL Server. Please find the attached SQL Script for the preceding example. In my next article of Joins I'll discuss advanced joins where we'll see how to retrieve non-matching rows using a LEFT, RIGHT and FULL Join, until then keep learning.

Please provide your valuable feedback and comments that enable me to provide a better article the next time.