SQL Queries – Differences Between WHERE Clauses and JOINs

sql

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?

Best Answer

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

Related Topic