Why JOIN ... USING clauses can be a bad habit

So first, let me say that I do like the JOIN ... USING clauses. They make straight forward joins look a lot better. The problem is if you get into a habit of using them and doing further join filtering in the WHERE clause. Most of the time, this is perfectly okay because the relational calculus handles things nicely. But what if your SQL becomes more complicated based on certain options? Let's look at an example. (This story is based on a subtle defect I had to work on today.)

Given two tables :

users
user_id	SERIAL
some_flag	BOOLEAN

user_attributes
user_attribute_id	SERIAL
user_id	FORIEGN KEY
name	VARCHAR
value	VARCHAR

Such that users have 0..n user_attributes (don't complain about this schema design, it's just for example).

You need a report that shows all users with a certain attribute, easy :


Bam! Done. Oh wait, the product owner wants an option to display all users that don't have that attribute set. Still easy :



...and done again! You're a master programmer. Of course, product owners are never happy. Now they want another option to display all users and that have that attribute or have some_flag set. That's easy too :



And done! I'm going home.
...
...
...
What do you mean the report isn't working?

So, because of "(name='CLOWN_ATTRIBUTE' OR some_flag = TRUE)" things aren't quite working right. The OR condition allows it to connect to any row in the user_attributes table that belongs to a user with some_flag set. While one can probably use some fancy SQL logic to make this still work with the JOIN ... USING clause, I find it much easier to :



Oh well, I know to look out for one more thing now.