Querying data from multiple tables is very common when working with relational databases. it’s not hard if you know how to use the dedicated sql operators to do this. In this article, you will learn how to join two tables using where and using a special join operator, and how to filter rows in the result set.
If you want to practice joining tables in sql, check out our interactive sql join course. offers more than 90 practical exercises on different types of joints. for more sql practice courses, check out our sql practice track.
Reading: How to connect tables in sql
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 all rows of this table, you can use the query:
In this simple query, the names of the columns you want to retrieve data from are listed after you select. next is the keyword of 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:
in this case, the result set consists of only two rows with name and price columns:
Columns are listed after the select, then the keyword helps specify the table from which to retrieve data. at the end of the query, the where keyword is followed by the filter condition. in this example, the condition compares the value in the price column to 250.00. the query returns details about a product only if the price of that product is less than 250.00.
query data from multiple tables using where
Relational databases are created with multiple tables that refer to each other. rows in one table refer to specific rows in another table, which are connected by some id columns. now we will see 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:
Let’s say you need some details from this warehouse database, such as the name of the products, the price and their respective categories. you can join rows from the product table with rows from the category table using a where clause. take a look at the query below:
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 corresponding table name, separated by a period.
table names are then listed after the from keyword, 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 id_category column of the product table correspond to the values of the id column of the category table, and the rows are joined when the values are equal ( product.category_id = category.id). the smartwatch in the product table has a category id of 2. the same value in the id column in the category table points to “electronics” as highlighted in green above.
if there are columns with the same name in both tables, they must be distinguished when you name them in select. you do this by naming the table, followed by a period, and then the column name. in our example, however, the tables have different column names with none in common. we can use only the column names in the select without specifying which tables they come from, like in the query below:
using where is a way to query data from multiple tables. it’s an older sql standard; while it is still available, it is rarely used anymore. in the next section, we’ll look at another method.
query data from multiple tables using join
Today, the most common method of joining data from multiple tables is with special operator joins, also known as inner joins. To see how it works, we’ll use the same two tables from the warehouse database, which you can find below for your convenience.
See also: How do I use GPS on my Fitbit device?
Take a look at the following query, which maps the category from the category table to each product name in the product table.
the join is performed by the join operator. in the from clause, the name of the first table (product) is followed by a join keyword and then the name of the second table (category). this is followed by the on keyword and the condition to join the rows from the different tables. the category name is assigned based on the column id of the categories table, which is equivalent to category_id in the products table (product.category_id=category.id).
here is the result set:
smartwatch has a category_id value of 2. in the id column of the category table, the value 2 is associated with electronics, so smartwatch maps to electronics.
Using the join operator is the most common method of joining multiple tables in a database. In the article “An Illustrated Guide to SQL Inner Join”, I discuss more about this operator. the article “sql inner join explained in simple words” also delves into this topic. As mentioned above, 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 you to filter 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:
here is the result:
The first part of this query is the same as the last part. in this query, however, products from the “toys” category are excluded. we filter the rows in the result set using a where clause to check if the category is something other than “toys” (category.name != ‘toys’).
For more sql join practice, I encourage you to check out our sql join hands-on course, read the “how to practice sql joins” article, or watch the “sql join basics” video in the series “we learned sql ”.
There are also other combination operators. In addition to the 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 achieve this using where or using join. To learn more about the differences between these methods, I recommend a very interesting article, “What’s the difference between having multiple tables from and using join?”.
I’ve only given you a glimpse of this topic here, so keep learning!