SQL: Inner Joins

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

It is the most commonly-used way to merge data together in SQL. The Inner Join is used to select data from one or more tables for viewing or analysis. This lesson will describe the concept and provide syntax examples.

A Common Join

The INNER JOIN is the most common SQL join. In fact, it's so common it's also called a simple join. But this doesn't mean it isn't powerful. The query will retrieve all rows of data from tables in which a specified condition matches on both tables. If you have a customer table and an orders table, an INNER JOIN query could be written to return all customer names who have ordered a specific product.

Example

Let's use an example of a music database, where we store artists, albums, genre information, etc. We want to join some album data with genre data; in our database, these tables share the genreID key. The following figure shows the data that we'll be extracting from the two tables:


SQL Inner Join Visualization


Another way to state the visualization is that the query will return records where the tables intersect. It is the set of data that is equal when the tables are joined together. Thus the term INNER JOIN!

In order to see how the join would work in practice, let's imagine our album table as follows:

albumID artistID albumTitle releaseDate genreID
15 30 Rattle and Hum 1988 27
25 90 Folk Favorites 1993 36
30 95 Mozart's Greatest Hits 2005 12

And the genre table stores the following information:

genreID genre
27 Rock
36 Folk
12 Classical

In order to conduct an INNER JOIN on the previous tables, we can create a simple SQL query. The full syntax of the query is as follows:


SELECT albumTitle, genre
FROM tblAlbum
INNER JOIN tblGenre
ON tblAlbum.genreID = tblGenre.genreID


Let's take a look at each keyword (the words that are in ALL CAPS in the code):

SELECT

Here we specify which tables/fields to join: The name of the table is written, followed by a period, and then the field(s) you want to view. The fields must exist in the tables you specify!

FROM

List the FIRST table you are querying from. In our example, we have decided to query the album and the genre table, in that order. Therefore, we'll start with the album table (tblAlbum).

INNER JOIN

This is the namesake of the statement. Here we state the SECOND table we are querying. In our example, we select the genre table (tblGenre).

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support