SQL: Using JOINS in SQL Server

SQL: Using JOINS in SQL Server

JOINS
Introduction

 

What is a JOIN?

JOINS in SQL Server are used to retrieve data from two of more related tables.  Tables are generally related by foreign key restraints.

We will discuss the different types of JOINS:

  • INNER JOIN
  • OUTER JOIN
  • FULL JOIN
  • CROSS JOIN

OUTER JOINS are divided into:

  1. LEFT JOIN or LEFT OUTER JOIN
  2. RIGHT JOIN or RIGHT OUTER JOIN
  3. FULL JOIN or FULL OUTER JOIN

What is a JOIN?

JOINS in SQL Server are used to retrieve data from two of more related tables.  Tables are generally related by foreign key restraints.

Here are two tables that I’ve created, the Employee and Department table.  I will refer back to these tables to create the different types of JOINS mentioned above.  Lets get started.

Employee Table

ID Name Gender Salary DepartmentID
1 Tom Male 4000 1
2 Pam Female 3000 3
3 John Male 3500 1
4 Sam Male 4500 2
5 Todd Male 2800 2
6 Ben Male 7000 1
7 Sara Female 4800 3
8 Valerie Female 5000 1
9 James Male 6500 NULL
10 Russell Male 8800 NULL

 

Department Table

ID DepartmentName Location DepartmentHead
1 IT London Rick
2 Payroll Delhi Ron
3 HR New York Christie
4 Other Department Sydney Cinderella

 

INNER JOIN
Introduction

 

  • INNER JOIN returns only matching rows between two tables. Non matching rows are eliminated.

Example:

Let’s say you we want only Name, Gender, Department Name, columns with Employee-Department tables.  How do we do this?

 

Note: Don’t forget to create a foreign key constraint on the Employee table DepartmentID in order to JOIN the tables together.

Syntax:

–Create foreign key–

Alter table Employee Add Constraint Employee_DepartmentID_FK

Foreign Key(DepartmentID) References dbo.Department (ID)

 

–How to create an INNER JOIN–

SELECT Name, Gender, Salary, DepartmentName

FROM dbo.Employee

INNER JOIN dbo.Department

ON dbo.Employee.DepartmentID =  dbo.Department.ID

Notice: when you run the query it displays only the matching records in both tables and discards the NULL values.

INNER JOIN

This is the desired outcome we want. (See example below)

Here’s a better picture of the table we wanted with Name, Gender, Department Name, columns using INNER JOIN:

INNER JOIN table

 

Let’s take a look at LEFT JOIN.

LEFT JOIN
Introduction

 

  • Returns all the matching rows + non matching rows from the LEFT table.

 

Example:

I want to retrieve all matching rows from Name, Gender, Salary, and Department Name from the Employees Table.

Syntax:

–LEFT JOIN–

Select Name, Gender, Salary, DepartmentName

FROM dbo.Employee1

LEFT JOIN dbo.Department

ON dbo.Employee1.DepartmentID = dbo.Department.ID;

Here’s the result:

All matching and non-matching rows including NULL.

LEFT JOIN

 

Let’s take a look at RIGHT JOIN.

RIGHT JOIN
Introduction

 

RIGHT JOIN will return all matching rows + nonmatching rows from the right table.

 

Example:

I want to retrieve the Name, Gender, Salary and DepartmentName using RIGHT JOIN.  How many records will it return?  Let’s take a look.

Syntax:

SELECT Name, Gender, Salary, DepartmentName

FROM dbo.Employee1

RIGHT OUTER JOIN dbo.Department

ON dbo.Employee1.DepartmentID = dbo.Department.ID;

RIGHT JOIN

 

Let’s take a look at FULL JOIN.

FULL JOIN
Introduction

 

  • FULL JOIN returns all the matching rows from the left and right table + nonmatching rows.

 

Syntax:

SELECT Name, Gender, Salary, DepartmentName

FROM dbo.Employee1

FULL JOIN dbo.Departement

ON dbo.Employee1.DepartmentID = dbo.Department.ID;

FULL JOIN

 

Now let’s see how to create a CROSS JOIN.  This one is very different from the others.  Let’s see why.

 

CROSS JOIN
Introduction

 

  • CROSS JOIN produces a Cartesian product of the two tables involved in the JOIN. What do I mean?  For example, in the Employee table we have 10 rows and in the Department table we have 4 rows, therefore a CROSS JOIN between these two tables will produce 40 rows.
  • Note: A CROSS JOIN shouldn’t have an ON clause.

 

Syntax:

SELECT Name, Gender, Salary, DepartmentName

FROM dbo.Employee1

CROSS JOIN dbo.Department;

 

CROSS JOIN 1

CROSS JOIN 2

CROSS JOIN 3

Next article I will feature:

  • more advanced JOINS

Thanks for reading!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s