Equality in JOIN or WHERE clause? Efficiency and style

Question

Of the two queries below, which is the most efficient for postgresql? And which has better style in terms of readability, etc.

The difference is the placement of the statement doctor.type != 'surgeon'

Inside the WHERE clause:

SELECT practice.name, doctor.name 
FROM doctor 
JOIN practice ON (doctor_code = code) 
WHERE doctor.name LIKE '%son' 
AND (doctor.type != 'surgeon');

or inside the JOIN clause:

SELECT practice.name, doctor.name
FROM doctor
JOIN practice ON (doctor_code = code AND doctor.type != 'surgeon')
WHERE doctor.name LIKE '%son';

Answer 1

Speaking of style, you can improve in several places:

In addition to what Craig already wrote.

• It's inconsistent to have one condition that only involves the doctor table in the JOIN clause, while the other one is in the WHERE clause. Be consistent, both or none. Best to put these in the WHERE clause, while the condition that links both tables goes into the JOIN clause - possibly simplified using USING. See below.

• It's also inconsistent to table-qualify some columns (doctor.name, doctor.type), but not others (code). In bigger queries its best to always table-qualify all columns to avoid naming collisions.

• "name" is not a good name. You should rarely use it. Use meaningful identifiers that you don't repeat in every table. Once you join a couple of tables (which is what you do in SQL all the time), you end up with multiple columns named "name" and have to deal out column aliases to even work with it. "id" or "code" are similarly bad choices.
  Use descriptive identifiers as suggested below.

• It's an antipattern to use doctor.code and practice.doctor_code. Some not so smart ORMs are in the habit of using this - doesn't make it a good idea. In addition to "code" being a bad identifier, it's good practice to use the same name for columns with identical data. Use doctor_code in both places. Or better yet: doctor_id, because what's in the column is probably an "identifier" not a "code" (wrong term). Such a naming convention also allows to use the convenient USING construct in joins.

• Use the SQL standard <> instead of the equivalent !=.

• Use table aliases to shorten code and maintain readability.

Everything put together, it could look like this:

SELECT p.practice, d.name  -- for a person, "name" is halfway sensible
FROM   doctor d
JOIN   practice p USING (doctor_id)
WHERE  d.name LIKE '%son'
AND    d.doc_type <> 'surgeon';

Answer 2

None of those parentheses are necessary.

Efficiency is identical for an inner join (so long as you're under join_collapse_limit). For join-lists bigger than PostgreSQL's join-reordering limit the extra terms in the ON predicate may be faster though.

Focus on style. Is it logically a part of the condition that joins one table to another? Put it in the ON predicate. Is it unrelated? Put it in the WHERE clause.