News for hackers. Stuff that matters.
Re:Best practices for writing SQL queries by carbocation on Saturday April 10, 2021 @01:23PM (#26763195)
Personal habit is to start my WHERE clause with a TRUE or a FALSE so that adding or removing clauses becomes seamless:

    SELECT foo
    FROM bar
    WHERE TRUE
      AND baz > boom
For OR conditions it's a bit different:

    SELECT foo
    FROM bar
    WHERE FALSE
      OR baz > boom
Re:Best practices for writing SQL queries by warent on Saturday April 10, 2021 @01:26PM (#26763214)
this seems like taking on a pretty huge risk for a minor convenience. the difference between those two queries can mean the difference between protecting someone's PII
Re:Best practices for writing SQL queries by carbocation on Saturday April 10, 2021 @02:22PM (#26763700)
I'm not sure that I follow. The two queries are to demonstrate difference in form; they are not intended to be equivalent.

If you're already writing:

    WHERE foo=bar
      AND biz=baz
It's not clear to me how:

    WHERE TRUE
      AND foo=bar
      AND biz=baz
is worse.
Re:Best practices for writing SQL queries by shazzdeeds on Saturday April 10, 2021 @03:22PM (#26764251)
He’s saying if someone gets in the habit of using that style they have to be very careful. If they forget to change True to False when using an OR that it could have major consequences. Performance being the least of concerns.
Re:Best practices for writing SQL queries by carbocation on Tuesday April 13, 2021 @09:53PM (#26802071)
I agree that such an error would be of the catastrophic type. It's interesting that several people seem to perceive this formatting approach as something that would increase the risk of that error. Is the red flag for people the WHERE TRUE on one line? Like, would this be less alarming to people?

    WHERE
      TRUE
      AND x=y
Re:Best practices for writing SQL queries by mactrey on Saturday April 10, 2021 @02:47PM (#26763926)
I'm so attached to starting all my where clauses with a TRUE (1=1 since SQL Server doesn't have boolean literals) that I do this when I need some OR clauses:

    SELECT foo
    FROM bar
    WHERE 1=1
      AND (1<>1
           OR baz > boom
           OR fizz >= bang
           )
      AND foo is not null
So you can comment out lines starting with OR individually. Some people might hate it but it makes sense conceptually for me since almost every query I write takes a chain of ANDs in the where clause as a starting point.
Re:Best practices for writing SQL queries by magicalhippo on Saturday April 10, 2021 @02:17PM (#26763654)
Yeah, I almost always do "where 1=1" with the actual expressions AND'ed below.

For OR, I like to keep the "1=1" and do

    AND (1=2
      OR ...
    )