32

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?

  • 3
    I don't think there is, but you might try doing an EXPLAIN to see if there's any difference in the query execution. – GrandmasterB Jan 15 '15 at 18:57
  • 6
    I'd like to point the conflicting signals of a work with "the Hard Way" in the title skipping a concept "because they are insanely confusing". But maybe just my concept of what "the hard way" should be is wrong. But again, maybe not. – Mindwin Jan 16 '15 at 12:31
  • 7
    JOIN very nicely transports the intention (joining tables) this leaves the WHERE part for actual filters and makes it just a bit easier to read. (besides maaany other implications) – Th 00 mÄ s Jan 16 '15 at 14:50
  • 2
    You are learning SQL the Hard Way if the author can't be bothered to write simple joins! As ThomasS says by using JOINs the intentions are made clearer, and the WHERE clauses become much simpler. Also using JOINs better illustrates set theory which underpins SQL. – Daniel Hollinrake Jan 21 '15 at 11:35
  • 1
    Not sure how I feel about something that purports to teach you something while saying "But hey we're going to skip this fundamental concept because it's craaazzzyyyy bananas." I think I'd end up looking for a different source to learn from. At some point you'll need to do outer joins and cross joins and should know how to do them. – Maurice Reeves Jan 23 '15 at 12:10

11 Answers 11

23

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 =*.

  • 5
    The reason the JOIN statement was added was because there was no standard for expressing outer joins, so each database vendor had their own "special" (incompatible) syntax for it. IIRC Oracle had *= or =* indicating left or right outer joins, another one I used only supported left outer joins using an |= operator. – TMN Jan 15 '15 at 21:14
  • 1
    @TMN IIRC Oracle used += or maybe it was =+. I believe *= was Transact-SQL (Sybase and later MS-SQL). Still, good point. – David Jan 16 '15 at 13:56
  • 1
    Where it starts getting complicated (IMHO) is when you have a mix of inner and outer joins. In that type of situation, I will confess I do sometimes fall back to the "low-class" technique of performing my joins in the WHERE clause. (I've heard this referred to as a theta join, but I'm not sure if that's correct.) – David Jan 16 '15 at 14:03
  • IIRC operators like "greater than" or "equal to" were sometimes referred to as "theta operators", but a google search leads to some operation in calculus. – Walter Mitty Jan 31 '15 at 19:50
12

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).

  • 9
    +1 For other types of joins. Most of my joins are either INNER JOIN or LEFT OUTER JOIN. They're not "insanely confusing." SQL can get insanely confusing, but this isn't an example of it. – mgw854 Jan 15 '15 at 18:50
  • off topic but should the statement be different types of joins or types of join ? – user1451111 Jul 3 '18 at 4:16
9

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.

5

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.

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.

  • 7
    It's a relational database, so the relations are pretty important to a query. I personally find it much harder to make sense of a query that mixes true filters (foo.x = 5) with relationships (foo.x = bar.x). The engine can easily optimize this into a join, but a human essentially has to reason about it row-by-row, as opposed to sets and subsets. – Aaronaught Jan 16 '15 at 4:36
4

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:

enter image description here

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.

  • I learned INNER JOINs long before I realized that the variant with the WHERE clauses is equivalent, and both of your examples look very readable to me. The one with the WHEREs and the commas might be even more readable. Where it falls down, I think, is in large complex queries, not these relatively simple ones. – Robert Harvey Jan 19 '15 at 0:38
  • Point is, to think that the comma variation isn't a relational join is not correct at all. – Thomas Stringer Jan 19 '15 at 2:52
  • I think you're incorrectly interpreting the commas as joins. The commas merely separate tables; it's the WHERE conditions that create the joins, not the commas. – Robert Harvey Jan 19 '15 at 3:28
  • 1
    I can most definitely say that there is no joining whatsoever happening in the predicate clauses. I think you're incorrectly interpreting the constructs of your relational query. Have you tried your comma joining without the WHERE clauses? It still works. It's a cartesian join. What do you think you're gaining by using commas? Please don't say that you're trying to save characters. – Thomas Stringer Jan 19 '15 at 14:28
  • 1
    I would say the first one is better because your intentions are clearer. There is much less ambiguity. – Daniel Hollinrake Jan 21 '15 at 11:41
4

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.

  • 2
    Since I've heard this refrain several times now, let me play devil's advocate. Learn X the Hard Way is about having technical depth; anyone with a good understanding of SQL really should know that the two approaches are equivalent, in terms of the output they produce. – Robert Harvey Jan 16 '15 at 21:57
  • 1
    I can see that, but the author isn't simply asserting that they're equivalent statements to a decent SQL server; they're asserting that using JOIN is "confusing", which is a path down which dirty code waits. ("No, don't use LINQ, just write your FOR statement by hand." "The compiler doesn't care about what I call this method, so there's no reason not to name it FN1") – DougM Jan 22 '15 at 22:20
3

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.

  • 3
    "Learn X the Hard Way" is a different learning approach. You write the code, and then understand it later. – Robert Harvey Jan 15 '15 at 18:56
  • 7
    @RobertHarvey That's not a different learning approach, its the standard one. Later only happens if you happen to be still in place when the wheels come off. dealt with way too many people writing SQL who think a table is rectangular array of cells to have any confidence in this method. – Tony Hopkinson Jan 15 '15 at 19:04
2

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.

  • Good answer, though whether you use the WHERE or put the clause in the JOIN statement can actually have a performance impact depending on the Query Optimizer. I've seen it happen more than once. – Locke Jan 15 '15 at 18:54
  • My experience with the performance impact is this: implicit joins will allow the query optimizer more options to optimize the query, which may seem like a good thing, but can be a problem. Specifically, the query optimizer may tune the query one way in development and another in production. The optimizer may be fooled into tuning that reduces performance. My recommendation is to use explicit join syntax AND confirm that the join is using columns that have indexes such that the performance is predictable. – Michael Potter Jan 16 '15 at 15:24
2

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.

0

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....

protected by gnat Jan 31 '15 at 20:29

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.