Click here to Skip to main content
15,867,488 members
Articles / Programming Languages / SQL

Get Ready to Learn SQL: 12. Introduction to Database Joins

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
22 Oct 2014MIT7 min read 9.1K   11   1
Introduction to database joins in SQL

Introduction

One of the biggest issues beginning SQL writers have is being able to write queries that use more than one table. In this series of articles, we are going to show you how to write a query that combines, or joins, data from more than one table. Once you have gone through the examples, you will understand how to write the basic commands to make this happen and why data is separated in the first place.

This first article introduces the concept of joining tables. The focus is going to be more on the type of joins, not necessarily their syntax. The later articles focus on the various types of joins. Through the narrative and examples, you’ll become very comfortable with each one.

In my prior articles, you learned about the need to normalize to make it easier to maintain the data. Though this makes it easier to maintain and update the data, it makes it very inconvenient to view and report information. Typically, the information you need to see has to be cross referenced across several tables for you to see the full picture.

Image 1

Left in separate tables, it’s tough to put it all together and understand what the data means.

Through the use of joins, we can stitch the data back together to make it easy for a person to use and understand.

So Why Combine Data?

Before we begin, let’s look into why you have to combine data in the first place. SQLite and other databases such as Microsoft SQL server and MySQL are relational databases. These types of databases make it really easy to create tables of data and a facility to relate (join or combine) the data together.

When databases are developed, care is taken to ensure redundant data is minimized. The databases go through a process called normalization that help reduce each database table to a single meaning or purpose.

For instance, if I had a table containing all the students and their classes, then wanted to change a student’s name, I would have to change it multiple times, once for each class the student enrolled in.

Normalizing, separate the data into a Student and Classes table. This makes it really easy to update the student name, but the price for this is that we have to piece the data back together to answer most of the questions we ask the database.

That is exactly why we need joins.

Database joins are used to match rows between tables to allow us to stitch the database back together to make it easy to read and use. In most cases, we’re matching a column value from one table with another.

Mechanics of a Join

When broken down, the mechanics of a join are pretty straightforward. To perform a join, you need two items: two tables and a join condition. The tables contain the rows to be combined, and the join condition the instructions to match rows together.

Take a look at the following Venn diagram. The circles represent the tables and where they overlap rows satisfying the join condition.

Image 2

You may be wondering what makes up a join condition. In many cases, a join condition is just matching one or more fields in one table to those in another. This type of join is sometimes called an equijoin since the only type of comparison used is the equals sign.

Joins aren’t limited to just exact matches such as the equijoin. You’ll see in later articles where it is useful to use other comparison operators such as the greater than sign.

A common join pattern you’ll come across is joining the primary key of one table to its foreign key. I suppose this is self-evident, but I think it is important to highlight it. Can you think why this is the case?

What process do we use to break up our data?

If you guessed normalization, you are correct. Through that process, we break up dependencies within tables to eliminate update anomalies among other things, but in order to keep relationships, we introduce foreign keys.

Let’s take an example from the sample database. Consider the following data model involving the Employees and Orders table. In this model, each employee can place zero or more orders.

Image 3

The EmployeeID is the primary key in the Employees table and foreign key in the Orders table. For each employee, there can be none, one, or perhaps many orders.

Here is a list of all the employees. To keep it simple, only the LastName is shown.

Image 4

In the sample database, you could write the following statement to get these results:

SQL
SELECT EmployeeID, LastName
FROM Employees

And here are the Orders.

Image 5

You can see this data using this select statement:

SQL
SELECT OrderID, EmployeeID, ShippedDate
FROM Orders

To create a report of employee LastName and the ShippedDate of the orders they placed, we need to combine information from both tables. To do so, we would create a join condition between the two tables on EmployeeID.

When we work with select statements involving more than one table, we need a way to keep really clear which field is from which table. This is done by placing the table name in front of the column name. A period is used to separate the two.

Using this convention, the join condition used is:

SQL
Employees.EmployeeID = Orders.EmployeeID

Check the following diagram. We join the table together we are looking for rows where the EmployeeID matches. So, for every order, where the EmployeeID = 4, the database will match the Employees table and match the corresponding row. In this case, that is the employee whose last name is “Baker.”

Image 6

This type of join is called an inner join and I’ll go through the explanation in detail in a later article, but if you are dying to try it now, here is the command!

SQL
SELECT Employees.LastName, Orders.ShippedDate
FROM Employees
INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID

There are several type of joins we can use to combine tables together. They are introduced in the next section.

Types of Database Joins

The joins we’ll cover in this section are characterized by which rows are returned when the join condition is met. We already know that an inner joins return rows from each table that meets the join table, but there are also joins that return rows from the tables even if the join condition isn’t met. These are called outer joins.

Inner Joins

Inner joins return rows where the join condition is met. This is the most common join. In its most common example, where a primary key is being matched to a foreign key, only combinations of rows matching the keys are returned.

Outer Joins

Outer joins are used when, in addition to returning the results of the inner join, you want to return rows from one table that don’t match in another. In the example above, an employee can have placed zero or more orders. If we only use an inner join, then our results only include employees that have place orders. Those that haven’t are included in the results.

There may be cases where we want to list all employees regardless of whether they placed an order. In this case, an outer join comes in handy. Regardless of the match with the Orders table, an employee record’s selected columns are listed in the results.

There are three types of outer joins: Left, Right, and Full outer joins.

  • Left Outer Join – Return all rows from the “left” table, and matching rows from the “right” table. If there are no matches in the right table, return Null values for those columns.
  • Right Outer Join – Return all rows from the “right” table, and matching rows from the “left” table. If there are no matches in the left table, return Null values for those columns.
  • Full Join – Return all rows from an inner join, when no match is found, return nulls for that table.

What’s Next?

In the following articles, we dig into the various join types, explore joins involving more than one table, and further explain join conditions, especially what can be done with non-equijoin conditions.

Also, I think it is important to understand what happens under the covers. So as part of this series, we’ll explore the impact joins have on database performance, and why it is important to understand whether indexes can help reduce query times.

More tutorials are to follow! Remember! If you have other questions you want answered, then post a comment or tweet me. I’m here to help you. What other topics would you like to know more about?

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
Generalnice Pin
Neel55555526-Oct-14 19:38
Neel55555526-Oct-14 19:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.