How do you Create a query in two tables?

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the "Orders" table:

OrderIDCustomerIDOrderDate

10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Then, look at a selection from the "Customers" table:

CustomerIDCustomerNameContactNameCountry

1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Try it Yourself »

and it will produce something like this:

OrderIDCustomerNameOrderDate

10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Berglunds snabbköp	8/12/1996

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

     

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:

idnameprice

1	Smartwatch Erin	234.00
2	Smartwatch Sun	455.00
3	Smartband Eli	300.00
4	Smartband White	124.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 Erin	234.00
Smartband White	124.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

1	smartwatch	235.00	2
2	bricks	26.70	3
3	lamp	128.00	2
4	sofa	3200.00	1
5	desk	1350.00	1
6	power strip	29.00	2

category

idcategory_name

1	furniture
2	electronics
3	toys

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

smartwatch	235.00	electronics
bricks	26.70	toys
lamp	128.00	electronics
sofa	3200.00	furniture
desk	1350.00	furniture
power strip	29.00	electronics

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

idnamepricecategory_id

1	smart watch	235.00	2
2	bricks	26.70	3
3	lamp	128.00	2
4	sofa	3200.00	1
5	desk	1350.00	1
6	power strip	29.00	2

category

idname

1	furniture
2	electronics
3	toys

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:

product_namecategory_name

smart watch	electronics
bricks	toys
lamp	electronics
sofa	furniture
desk	furniture
power strip	electronics

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 watch	electronics
lamp	electronics
sofa	furniture
desk	furniture
power strip	electronics

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!

How do you write a query in two tables in SQL?

Can you query from multiple tables?

How do you create a multiple query in Access?