Back to articles list September 16, 2020 - 6 minutes read How to Join Two Tables in SQL Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: sql learn sql JOIN 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. How to Query a Single Table First, a quick refresher on how to select data from a single table. Consider the following table, product: idnameprice 1Smartwatch Erin234.00 2Smartwatch Sun455.00 3Smartband Eli300.00 4Smartband White124.00 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: nameprice Smartwatch Erin234.00 Smartband White124.00 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 idproduct_namepricecategory_id 1smartwatch235.002 2bricks26.703 3lamp128.002 4sofa3200.001 5desk1350.001 6power strip29.002 category idcategory_name 1furniture 2electronics 3toys 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: product_namepricecategory_name smartwatch235.00electronics bricks26.70toys lamp128.00electronics sofa3200.00furniture desk1350.00furniture power strip29.00electronics 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 “toys” as highlighted in blue 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 idnamepricecategory_id 1smart watch235.002 2bricks26.703 3lamp128.002 4sofa3200.001 5desk1350.001 6power strip29.002 category idname 1furniture 2electronics 3toys 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 d table, which is equivalent to category_id in the table product (product.category_id=category.id). Here is the result set: product_namecategory_name smart watchelectronics brickstoys lampelectronics sofafurniture deskfurniture power stripelectronics 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: product_namecategory_name smart watchelectronics lampelectronics sofafurniture deskfurniture power stripelectronics 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 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” and learn how to use them in our course “SQL Basics”. 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! Tags: sql learn sql JOIN You may also like SQL JOINs A SQL JOIN is a method to retrieve data from two or more database tables. Here we present a basic overview of what data from a particular SQL join will look Read more Can you Join two Tables Without a Common Column? Do you need to combine two tables without a common column? Learn how the CROSS JOIN and the UNION operators can help you with this task. Read more Learning JOINs With Real World SQL Examples JOIN statement lets you work with data stored in multiple tables. This article is a practical introduction to the SQL JOIN. Check out examples in real life. Read more An Introduction to Using SQL Aggregate Functions with JOINs Aggregate functions. Powerful SQL tools. Let's see how they cooperate paired with LEFT JOIN, SUM and GROUP BY perform computations on multiple tables. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. Read more How to LEFT JOIN Multiple Tables in SQL You will learn how to left join 3 tables in SQL while avoiding common mistakes in joining multiple tables. Examples included! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.