DEV Community

Mark Sta Ana
Mark Sta Ana

Posted on • Originally published at booyaa.wtf on

5

Defensive coding in SQL

#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

Top comments (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”?