24th Jul 2020 7 minutes read

What's the Difference Between Multiple Tables in FROM and Using JOIN?

JOIN

Table of Contents

Joining Tables by Listing Them in FROM
Joining Tables Using the JOIN Keyword
Why Use the New Syntax for SQL Joins?
Time to Learn the New Syntax for SQL JOINs!

What’s your approach to joining tables in SQL? In this article, we discuss two approaches and explain why many SQL developers have a definite preference for JOIN.

Do you prefer to list multiple tables in FROM and use WHERE to set the join conditions? Or do you use the JOIN keyword? SQL supports both, but there are significant differences between them. Let’s look at each one in detail and then discuss why JOIN is generally preferred.

Joining Tables by Listing Them in FROM

Let’s say we run a toy shop and have two database tables that we want to join: toys and sales.

Toys

id	name	brand	price
1	Bumblebee	Transformers	14.99
2	Optimus Prime	Transformers	19.99
3	Lightning McQueen	Disney Cars	23.97
4	Ramone	Disney Cars	20.99
5	Wonder Woman	Barbie	39.99
6	Princess Leia	Barbie	99.99
7	Wizard of Oz: Glinda	Barbie	43.95

Sales

id	toy_id	employee_id	date	quantity
1	5	3	2020-07-01	1
2	1	1	2020-07-01	1
3	3	1	2020-07-02	1
4	6	3	2020-07-03	1
5	2	3	2020-07-03	1

We can simply list both tables in the FROM clause and state in the WHERE clause that the id from the toy table should match the toy_id from the sales table:

SELECT t.id, t.name, t.brand, t.price, s.quantity, s.date
FROM toys t, sales s
WHERE t.id = s.toy_id;

We’ll get the following result:

id	name	brand	price	quantity	date
1	Bumblebee	Transformers	14.99	1	2020-07-01
2	Optimus Prime	Transformers	19.99	1	2020-07-03
3	Lightning McQueen	Disney Cars	23.97	1	2020-07-02
5	Wonder Woman	Barbie	39.99	1	2020-07-01
6	Princess Leia	Barbie	99.99	1	2020-07-03

Everything worked as intended – we’ve joined two tables and got the results we were expecting.

This is definitely a working solution for joining tables in SQL. However, it uses an old syntax that was common before the SQL-92 standard introduced the JOIN keyword. Let’s now see how we can get the same result using a modern approach to joining tables.

Joining Tables Using the JOIN Keyword

We can use the following query with the JOIN keyword to do the same thing:

SELECT t.id, t.name, t.brand, t.price, s.quantity, s.date
FROM toys t
JOIN sales s
ON t.id = s.toy_id;

This syntax was introduced in the SQL-92 standard. Here, we have only one table in the FROM clause; the table we want to join is listed in the JOIN clause. Then, we have the ON keyword to specify the columns to be used for joining these tables.

The result of this query is the same as above. However, this approach has a number of advantages that make it more prevalent among SQL practitioners. If you prefer to use the old syntax for joining tables, check out the following arguments. They might change your mind.

Why Use the New Syntax for SQL Joins?

Let’s start by clarifying that performance-wise there is no difference between the two syntaxes. Using the JOIN keyword is not only a formal standard these days, it’s also a common practice among SQL users. So, what are the benefits of using the JOIN clause?

Join conditions are separate from filtering conditions

When you use the old syntax, your join conditions and filtering conditions are physically grouped together in the WHERE clause. This can cause some confusion.

For example, let’s say that we want to see only the sales from July 3rd. We’ll need to add one more condition to the WHERE clause:

SELECT t.id, t.name, t.brand, t.price, s.quantity, s.date
FROM toys t, sales s
WHERE t.id = s.toy_id AND s.date = '2020-07-03';

Now we have two conditions in WHERE, but only one of them is a true filtering condition. The other is just used for specifying the columns on which to join the tables.

With the JOIN keyword, the joining conditions are separated from the filtering conditions:

SELECT t.id, t.name, t.brand, t.price, s.quantity, s.date
FROM toys t
JOIN sales s
ON t.id = s.toy_id
WHERE s.date = '2020-07-03';

Here, we specify the join condition in ON and use WHERE to filter the results.

Both queries will output the same result, but the new syntax makes it clear where you are joining the tables and where you are filtering the results.

It’s easier to join multiple tables

Often, you’ll need to join multiple tables with SQL. For example, let’s say that we want to join the employees table to the toys and sales tables so we know who sold each toy.

Employees

id	name
1	Rob Stevens
2	Jane White
3	Sofia Clark

With the old syntax, we’d have the following query:

SELECT t.id, t.name, t.brand, t.price, s.quantity, s.date, e.name AS employee
FROM toys t, sales s, employees e
WHERE t.id = s.toy_id AND e.id = s.employee_id;

To join the same three tables with the JOIN syntax, we’d use this query:

SELECT t.id, t.name, t.brand, t.price, s.quantity, s.date, e.name AS employee
FROM toys t
JOIN sales s
ON t.id = s.toy_id
JOIN employees e
ON e.id = s.employee_id;

The result is the same:

id	name	brand	price	quantity	date	employee
1	Bumblebee	Transformers	14.99	1	2020-07-01	Rob Stevens
2	Optimus Prime	Transformers	19.99	1	2020-07-03	Sofia Clark
3	Lightning McQueen	Disney Cars	23.97	1	2020-07-02	Rob Stevens
5	Wonder Woman	Barbie	39.99	1	2020-07-01	Sofia Clark
6	Princess Leia	Barbie	99.99	1	2020-07-03	Sofia Clark

The “chain” of tables we want to join is more visible in the new syntax, where we first join two tables on specific columns and then join the third table using another join condition. With the old syntax, we have all three tables listed together in FROM, and then both join conditions specified (in any order) in WHERE. Which condition corresponds to which join, especially if we need to join more than three tables, quickly gets confusing in this syntax.

LEFT / RIGHT/ FULL JOINs are easier

JOIN syntax makes all kinds of outer joins very straightforward. You can make left (outer) joins, right (outer) joins, or full (outer) joins by simply using the respective keywords (LEFT JOIN, RIGHT JOIN, or FULL JOIN).

Let’s say we want to join the toys and sales tables so that we have all toys displayed in the result, even if there were no sales for a toy during the given period. You can do this join easily in any database by simply using LEFT JOIN:

SELECT t.id, t.name, t.brand, t.price, s.quantity, s.date
FROM toys t
LEFT JOIN sales s
ON t.id = s.toy_id;

The result will include all toys and the corresponding sales information, when applicable:

id	name	brand	price	quantity	date
5	Wonder Woman	Barbie	39.99	1	2020-07-01
1	Bumblebee	Transformers	14.99	1	2020-07-01
3	Lightning McQueen	Disney Cars	23.97	1	2020-07-02
6	Princess Leia	Barbie	99.99	1	2020-07-03
2	Optimus Prime	Transformers	19.99	1	2020-07-03
4	Ramone	Disney Cars	20.99	NULL	NULL
7	Wizard of Oz: Glinda	Barbie	43.95	NULL	NULL

If you’re new to LEFT JOINs, read this comprehensive guide on left-joining multiple tables.

And what about the old syntax? That’s complicated. Some databases may allow certain tricks for left-joining tables without using the JOIN keyword, but there is no consistent solution that will work in all cases.

For example, in Oracle you can use the (+) operator to make a left join:

SELECT t.id, t.name, t.brand, t.price, s.quantity, s.day
FROM toys t, sales s
WHERE t.id = s.toy_id (+);

However, this syntax doesn’t work in other databases.

Accidental CROSS JOINs are avoided

Another common issue with joining tables with FROM is the accidental CROSS JOIN. If you omit a join condition in WHERE, you’ll get all the rows in the first table combined with all the rows from the second table. Thus, the number of rows in the result set will be the multiplication of the number of rows in each table. Such an error can be tricky to detect and debug.

At the same time, when you use the SQL-92 standard to join tables, you’ll get a syntax error if you omit the join condition. Of course, you can cross-join your tables in the new syntax, but only if you explicitly use the corresponding keyword (CROSS JOIN).

Time to Learn the New Syntax for SQL JOINs!

Most SQL practitioners agree that the new syntax is more readable once you get used to it. Hopefully, you’re also convinced that the modern approach to joining tables in SQL is worth your attention.

LearnSQL offers a comprehensive course on SQL JOINs that includes 93 interactive exercises. You’ll get the opportunity to practice inner joins, all kinds of outer joins, non-equi joins, and self-joins.

In addition to exercises, you may find it useful to refresh your knowledge of SQL JOINs with our beginner-friendly guides:

Happy learning!

Tags:

JOIN