loading...

Defensive coding in SQL

booyaa profile image Mark Sta Ana Originally published at booyaa.wtf on ・1 min read

Always wrap ON clauses in parens to avoid predicates being deleted accidentally. The following code will scream if you delete the AND clause.

SELECT *
  FROM foo
  LEFT JOIN bar
    ON ((foo.id = bar.id)
        AND (foo.fizz = bar.buzz))

Where as the following won't.

SELECT *
  FROM foo
  LEFT JOIN bar
    ON foo.id = bar.id
        AND foo.fizz = bar.buzz

Discussion

pic
Editor guide
 

Interesting. I love defensive programming, but I’m not a SQL champ. So, what would the error text be? Like can you clarify what you mean by “code will scream”?