How to Join Two Tables in SQL

Querying data from multiple tables is very common when working with relational databases. It is not difficult if you know how to use the dedicated SQL operators for doing this. In this article, you will learn how to join two tables by using WHERE and by using a special operator JOIN, and you will see how to filter rows in the result set.

If you want to practice joining tables in SQL, check out our interactive SQL JOINs course. It offers over 90 hands-on exercises on different kinds of JOINs. The course covers simple 2-table joins, multiple joins, LEFT JOIN, FULL JOIN, self join, any many more. It is the most complete practical review of SQL JOINs available on the Internet.

How to Query a Single Table

First, a quick refresher on how to select data from a single table. Consider the following table, product:

To select data from all columns and from all rows in this table, you might use the query:

SELECT id, name, price 
FROM product;

In this simple query, the names of the columns from which you want to retrieve data are listed after SELECT. Next is the keyword FROM and the name of the table where the data is stored.

You can also filter rows to return only the records that match the given criteria. Take a look at the following SQL code:

SELECT name, price 
FROM product
WHERE price < 250.00;

In this case, the result set consists of only two rows with the columns name and price:

The columns are listed after the SELECT, then the keyword FROM helps specify the table from which you retrieve data. At the end of the query, the WHERE keyword is followed by the filtering condition. In this example, the condition compares the value in the column price to 250.00. The query returns the details about a product only If the price of that product is less than 250.00.

Querying Data From Multiple Tables Using WHERE

Relational databases are built with multiple tables that refer to each other. Rows from one table refer to specific rows in another table, which are connected by some ID column(s). We will now take a look at how to join data from one table with data from another table.

Consider the following two tables, product and category, in a database about products in the warehouse:

product

category

Let’s say you need some details from this warehouse database, like the name of the products, the price and their respective categories. You can join rows from the table product with rows from the table category using a WHERE clause. Take a look at the query below:

SELECT product.product_name, product.price, category.category_name 
FROM product, category 
WHERE product.category_id = category.id ;

Here is the result set:

The SELECT in this query lists columns from both tables: product_name and price from the product table and category_name from the category table. Each column name is preceded by the name of the corresponding table, separated by a period.

Next, the names of the tables are listed after the keyword FROM, separated by commas.

The last part of this query is a WHERE, with a condition that specifies how to join the rows from both tables. Here, the values of the column category_id from the table product correspond to the values in the column id from the table category, and the rows are joined when the values are equal (product.category_id = category.id). The smart watch in the table product has a category ID of 2. The same value in the column id in the table category points to “electronics” as highlighted in green above.

If there are columns with the same name in both tables, they need to be distinguished when you name them in SELECT. You do this by naming the table, followed by a period then the name of the column. In our example, however, the tables have different column names with none in common. We can use just the column names in SELECT without specifying which tables they come from, like in the query below:

SELECT product_name, price, category_name 
FROM product, category 
WHERE product.category_id = category.id ;

Using WHERE is one way to query data from multiple tables. It is an older SQL standard; while it is still available, it is rarely used anymore. In the next section, we will look at another method.

Querying Data From Multiple Tables Using JOIN

Today, the most common method for joining data from multiple tables is with the special operator JOIN, also known as INNER JOIN. To see how it works, we will use the same two tables from the warehouse database, which you can find below for convenience.

product

category

Take a look at the following query, which assigns the category from the table category to each product name in the table product.

SELECT
  product.name AS product_name,
  category.name AS category_name
FROM product
JOIN category ON product.category_id=category.id;

The join is done by the JOIN operator. In the FROM clause, the name of the first table (product) is followed by a JOIN keyword then by the name of the second table (category). This is then followed by the keyword ON and by the condition for joining the rows from the different tables. The category name is assigned based on the column id of the category table, which is equivalent to category_id in the table product (product.category_id=category.id).

Here is the result set:

The smart watch has the category_id value of 2. In the column id of the table category, the value 2 is associated with electronics, so the smart watch is assigned to electronics.

Using the JOIN operator is the most common method for joining multiple tables in a database. In the article "An Illustrated Guide to the SQL INNER JOIN", I discuss more about this operator. The article "SQL INNER JOIN Explained in Simple Words" also delves into this topic further. As mentioned earlier, the INNER JOIN operator is equivalent to JOIN; you can use them interchangeably.

Select Data From Tables Using JOIN and WHERE

Using JOIN operators to retrieve data from multiple tables also allows for filtering the result set more easily. Take a look at the following query, which is a variant of the previous query and is based on the same data:

SELECT
  product.name AS product_name,
  category.name AS category_name
FROM product
JOIN category ON product.category_id=category.id
WHERE category.name != ’toys’;

Here is the result:

The first part of this query is the same as the last one. In this query, however, the products of the category “toys” are excluded. We filter the rows in the result set by using a WHERE clause to check if the category is other than “toys” (category.name != ’toys’).

For more JOIN practice in SQL, I invite you to check out our hands-on SQL JOINs course, read the article "How to Practice SQL JOINs" or watch the “SQL JOIN Basics” video in the series “We Learn SQL”.

There are also other JOIN operators. In addition to JOIN or INNER JOIN, there are LEFT JOIN, RIGHT JOIN, and FULL JOIN operators. Read more about them in “SQL JOINs for Beginners”. For more SQL practice, check out our SQL Practice track.

Joining Tables in SQL

We have seen how you can retrieve full details about objects by joining multiple tables that refer to each other in a relational database. You can accomplish this by using WHERE or by using JOIN. For more on the differences between these methods, I recommend a very interesting article, “What's the Difference Between Having Multiple Tables in FROM and Using JOIN?”.

I’ve given you only a glimpse into this topic here, so keep learning!