DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 966,155 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
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

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

You’ve already scrolled down this far, why not join our community of 900k+ developers all learning together?