Is there any material difference between queries joined by WHERE clauses, and queries using an actual JOIN?

Question

In Learn SQL the Hard Way (exercise six), the author presents the following query:

SELECT pet.id, pet.name, pet.age, pet.dead
    FROM pet, person_pet, person
    WHERE
    pet.id = person_pet.pet_id AND
    person_pet.person_id = person.id AND
    person.first_name = "Zed";

and then goes on to say that:

There are actually other ways to get these kinds of queries to work called "joins". I'm avoiding those concepts for now because they are insanely confusing. Just stick to this way of joining tables for now and ignore people who try to tell [you] that this is somehow slower or "low class".

Is that true? Why or why not?

Answer 1

With the author's approach, teaching OUTER JOINs is going to much more difficult. The ON clause in INNER JOIN was never mind-blowing to me like a lot of other stuff. Maybe it is because I never learned the old way. I'd like to think there is a reason we got rid of it and it wasn't to be smug and call this method low class.

It's true in the very narrow scenario the author has created:

• Such an entry level of SQL that using ON is complex
• Only considering JOIN/INNER JOIN and not any OUTER JOINs
• The isolated coder who doesn't have to read other's code nor have any people with experience with the ON usage reading/using their code.
• Not requiring complex querying with lots of: tables, if's, but's and or's.

As part of a teaching progression, I think it is easier to break it down and have a natural progression:

Select * from table
select this, something, that from table
select this from table where that = 'this'
select this from table join anothertable on this.id = that.thisid

The concepts of joining and filtering tables are not really the same. Learning the correct syntax now will have more carry-over when you learn OUTER JOINS unless the author intends on teaching outdated/deprecated things like: *= or =*.

Answer 2

Whether it's slower depends on the Query Optimizer and how it streamlines the query (what you write isn't actually what is executed). However, the big problem with this quote is that it completely ignores the fact that there are different types of joins which operate completely differently. For instance, what is being said is (theoretically) true for inner joins, but it doesn't hold true for outer joins (left joins and right joins).

Answer 3

The author presents a simple case where either the old or new syntax can be used. I do not agree on his/her statement that joins are insanely confusing, because joining tables is a fundamental SQL query concept. So, maybe the author should have spent some time prior in explaining how JOINS work before uttering an opinionated statement as well as doing a multiple table query example.

One should use the newer syntax. The main argument for this is that your query will have:

• Select Criteria
• Join Criteria
• Filter Criteria

Using the old style, the join and filter criteria are combined which in more complex cases can lead to confusion.

Also, one can get a Cartesian product by forgetting a join criteria in the filter clause:

 person_pet.person_id = person.id

using the older syntax.

Using the newer syntax also specifies how the join should occur which is important on whether you want an INNER, LEFT OUTER, etc. so it is more explicit in regards to JOIN syntax which IMHO increases readability for those unfamiliar with joining tables.

Answer 4

There shouldn't be, the query parser should generate an equivalent internal representation for equivalent queries regardless of how they're written. The author's just using pre-SQL-92 syntax, which is why he mentions it might be seen as "old fashioned" or "low class". Internally, the parser and optimizer should generate the same query plan.

Answer 5

I learned SQL this way, including the *= syntax for outer joins. To me, it was very intuitive since all relations were given equal precedence and did a better job of setting up queries as a series of questions: What do you want? Where do you want them from? Which ones do you want?

By doing join syntax, it disrupts the thought process towards the relations more strongly. And personally, I find the code far less readable with the tables and relations intermingled.

At least in MSSQL, there's no meaningful difference in the performance of the queries, assuming you use the same join ordering. That said, there is one clear, huge issue with learning (and using) SQL this way. If you forget one of your relations, you would get unexpected cross-products. Which on a database of any non-trivial size is prohibitively expensive (and dangerous for non-selects!). It's much harder to forget a relation when using the join style syntax.

Answer 6

There are two different aspects of this to consider: Performance and Maintainability/Readability.

Maintainability/Readability

I chose a different query, as it is something that I think is a better/worse example than the original query that you posted.

What looks better to you and is more readable?

select
    e.LoginID,
    DepartmentName = d.Name
from HumanResources.Employee e
inner join HumanResources.EmployeeDepartmentHistory edh
on e.BusinessEntityID = edh.BusinessEntityID
inner join HumanResources.Department d
on edh.DepartmentID = d.DepartmentID
where d.Name = 'Engineering';

Or...

select
    e.LoginID,
    DepartmentName = d.Name
from HumanResources.Employee e, 
HumanResources.EmployeeDepartmentHistory edh,
HumanResources.Department d
where e.BusinessEntityID = edh.BusinessEntityID
and edh.DepartmentID = d.DepartmentID
and d.Name = 'Engineering';

For me personally, the first one is quite readable. You see that we are joining tables with INNER JOIN, meaning that we are pulling the rows that match on the subsequent join clause (i.e. "join Employee with EmployeeDepartmentHistory on BusinessEntityID and include those rows").

The latter, the comma means nothing to me. It makes me wonder what you are doing with all of those WHERE clause predicates.

The former reads more like my brain thinks. I look at SQL all day every day and the commas for joins. Which leads me to my next point...

There are actually other ways to get these kinds of queries to work called "joins"

They are all joins. Even the commas is a join. The fact that the author doesn't call them that is indeed their downfall.... it isn't obvious. It should be obvious. You're joining relational data, whether you specify JOIN or ,.

Performance

This is most definitely going to be RDBMS-dependent. I can only speak on behalf of Microsoft SQL Server. Performance-wise these are equivalent. How do you know? Capture the post-execution plans and see what exactly SQL Server is doing for each of these statements:

In the above image, I highlighted that I'm using both queries as above, differing only in the explicit characters for the join (JOIN vs ,). SQL Server does the exact same thing.

Summary

Don't use commas. Use explicit JOIN statements.

Answer 7

No, it's not true at all. The author is setting up his readers for confusion, and encouraging cargo-cult programming that avoids a very powerful structural difference between the standard syntax and this older variant he prefers. Specifically, a cluttered WHERE clause makes it harder to figure out what makes his query special.

His example leads a reader to generate a mental map of its meaning that has an awful lot of clutter.

SELECT pet.id, pet.name, pet.age, pet.dead
    FROM pet, person_pet, person
    WHERE
    pet.id = person_pet.pet_id AND
    person_pet.person_id = person.id AND
    person.first_name = "Zed";

Roughly, the above is:

Get the pet's ID, NAME, AGE, and DEAD for all pets, person_pet, and persons where the pet ID happens to match a person_pet's pet_id, and the person_id of that record happens to match the person_id of a person whose FIRST_NAME is "Zed"

With a mental map like that, the reader (who is writing the SQL by hand for some reason) may very easily make a mistake, possibly by omitting one or more tables. And a reader of code written in such a way will have to work harder, to figure out exactly what the SQL-author is trying to do. ("Harder" is on the level of reading SQL with or without syntax-highlighting, but it's still a greater than zero difference.)

There's a reason why JOIN's are common, and it's the old classic "seperation of concerns" canard. Specifically, for a SQL query there's good reason to seperate how data is structured vs how data is filtered.

If the query is written cleaner, such as

SELECT pet.id, pet.name, pet.age
FROM pet
  JOIN person_pet ON pet.id = person_pet.pet_id
  JOIN person ON person.id = person_pet.person_id
WHERE 
  person.first_name = "Zed";

Then the reader has a clearer distinction between the components of what's being asked for. The distinctive filter of this query is separated from how its components relate to each other, and the necessary components of each relation are directly next to where they are required.

---

Of course, any modern database system shouldn't see a meaningful difference between the two styles. But if database performance was the only consideration, the SQL query wouldn't have white space or capitalization, either.

Answer 8

Guy is making a classic error. He's trying to teach an abstract concept with a specific implementation. As soon as you do that you get into this sort of mess.

Should have taught basic database concepts first, then shown SQL as one way of describing them.

Left and right joins, could be argued they don't matter too much. Outer Join, well you could use the old *= and =* syntax.

Now you could argue that syntax is simpler, but only for simple queries. As soon as you start trying to do a complex query with this version, you can get in a horrible mess. The "new" syntax wasn't introduced so you could do complex queries, it was so you do complex queries in a readable and therefore maintainable way.

Answer 9

The example is equivalent to the simple reformulation with inner JOINs. The difference lies solely in the additional possibilities that the JOIN syntax allows. For instance, you can specify the order in which the columns of the two tables involved are processed; see e.g. https://stackoverflow.com/a/1018825/259310.

The received wisdom is, when in doubt, to write your queries in the way that makes them more readable. But whether JOIN or WHERE formulations are easier to read appears to be a matter of personal preference, which is why both forms are so widespread.

Answer 10

When I learned SQL, the INNER JOIN, LEFT JOIN, etc. forms didn't exist. As other answers have already stated, different dialects of SQL had each implemented outer joins using idiosyncratic syntax. This damaged portability of SQL code. Bringing the language back together required some change, and LEFT JOIN, etc. was what they settled on.

It's true that for every INNER JOIN, an equivalent comma join with the join condition in the WHERE clause can be written. It took me a while to migrate from liking the old form to preferring the new form. Apparently, the author of Learning SQL the Hard Way still thinks the old way is easier.

Are there any differences? Well, yes, there are. The first is that an INNER JOIN with an ON clause reveals the author's intent more clearly than the old style join. The fact that the ON clause is in fact a join condition and not some other kind of restriction is more obvious. This makes code that uses INNER JOIN easier to learn when reading than the old style. This is important when maintaining someone else's code.

The second difference is that the new style makes it marginally easier for the query optimizer to discover the winning strategy. This is a very small effect, but it's real.

The third difference is that when you learn using INNER JOIN (or just plain JOIN) it makes it easier to learn LEFT JOIN, etc.

Apart from that there is no material difference at all.

Answer 11

It depends if you think in terms of sets and formal logic.....

If you do then not using the "join" keyword makes for more simple progression from formal logic to SQL.

But if like 99% of people, you did not enjoy formal logic in your maths degree, then the join keyword is a let easier to learn. SQL used to be presented at university as just anther way to write down formal logic queries....