DEV Community

Mark Sta Ana
Mark Sta Ana

Posted on • Originally published at booyaa.wtf on

Defensive coding in SQL

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))
Enter fullscreen mode Exit fullscreen mode

Where as the following won't.

SELECT *
  FROM foo
  LEFT JOIN bar
    ON foo.id = bar.id
        AND foo.fizz = bar.buzz
Enter fullscreen mode Exit fullscreen mode

Discussion (1)

Collapse
cubiclebuddha profile image
Cubicle Buddha

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”?