4 ms·
Another trick is, if you're programmatically building a SQL statement - adding "WHERE 1=1" makes things easier ... like so: SELECT * FROM table WHERE 1=1
by tiffanyh 2y ago
Another trick is, if you're programmatically building a SQL statement - adding "WHERE 1=1" makes things easier ... like so:
SELECT *
FROM table
WHERE 1=1
That way, if you want to filter down the result, everything programmatically appended just needs an "AND ..." at the start, like:
SELECT *
FROM table
WHERE 1=1
AND age > 21
AND xyz = 'abc' ...
Because without "WHERE 1=1", you'd had to handle the first condition different than all subsequent conditions.
- dewey 2y agoYou can also just do "where true", easier to type.
- andy81 2y agoNot in e.g. MSSQL
- chasil 2y agoSimilarly, you could select NULL as your leading column, and prepend commas by that means. That method does impact the result set, and using it for CTAS or bulk insert would require more care in column selection.
- wruza 2y agoI prefer “1=0 OR 1=1”, because when you delete all conditions you can keep 1=0 out of selection and it decays into a no-op rather than destroying a table: DELETE FROM table WHERE 1=0[ OR 1=1 AND age > 21 AND xyz = 'abc'] ; Brackets designate selection bounds before text deletion. The above just safely does nothing after you hit DEL. Without that you’d have to delete whole [WHERE…], which leaves a very dangerous statement in the code.