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.

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').

Joining Tables in SQL

There are also other JOIN operators. In addition to JOIN or INNER JOIN, there are LEFT JOIN, RIGHT JOIN, and FULL JOIN operators. If you want to practice JOIN, check out the SQL JOINs course at LearnSQL.com. With over 90 hands-on exercises and clear examples, you’ll gain the confidence to use SQL joins effectively in real-world scenarios. Start today and boost your data skills with LearnSQL.com!