24th Jul 2020 7 minutes read What's the Difference Between Having Multiple Tables in FROM and Using JOIN? Kateryna Koidan sql learn 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 idnamebrandprice 1BumblebeeTransformers14.99 2Optimus PrimeTransformers19.99 3Lightning McQueenDisney Cars23.97 4RamoneDisney Cars20.99 5Wonder WomanBarbie39.99 6Princess LeiaBarbie99.99 7Wizard of Oz: GlindaBarbie43.95 Sales idtoy_idemployee_iddatequantity 1532020-07-011 2112020-07-011 3312020-07-021 4632020-07-031 5232020-07-031 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: idnamebrandpricequantitydate 1BumblebeeTransformers14.9912020-07-01 2Optimus PrimeTransformers19.9912020-07-03 3Lightning McQueenDisney Cars23.9712020-07-02 5Wonder WomanBarbie39.9912020-07-01 6Princess LeiaBarbie99.9912020-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 idname 1Rob Stevens 2Jane White 3Sofia 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: idnamebrandpricequantitydateemployee 1BumblebeeTransformers14.9912020-07-01Rob Stevens 2Optimus PrimeTransformers19.9912020-07-03Sofia Clark 3Lightning McQueenDisney Cars23.9712020-07-02Rob Stevens 5Wonder WomanBarbie39.9912020-07-01Sofia Clark 6Princess LeiaBarbie99.9912020-07-03Sofia 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: idnamebrandpricequantitydate 5Wonder WomanBarbie39.9912020-07-01 1BumblebeeTransformers14.9912020-07-01 3Lightning McQueenDisney Cars23.9712020-07-02 6Princess LeiaBarbie99.9912020-07-03 2Optimus PrimeTransformers19.9912020-07-03 4RamoneDisney Cars20.99NULLNULL 7Wizard of Oz: GlindaBarbie43.95NULLNULL 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: How to Learn SQL JOINs How to Practice SQL JOINs Learning JOINs with Real World SQL Examples Happy learning! Tags: sql learn sql joins