7 ms·
I'm an idiot sorry about that. Okay, let's try again, suppose you wrote SELECT * FROM t1 WHERE 2+2=4 Which is semantically identical to SELECT * FROM t1 Whi
by _a_a_a_ 3y ago
I'm an idiot sorry about that. Okay, let's try again, suppose you wrote
SELECT * FROM t1 WHERE 2+2=4
Which is semantically identical to
SELECT * FROM t1
While the first is correct in that it has a semantic meaning and a valid output, wouldn't you rather have the system warn you that you've written something redundant? Because it's hardly likely a human would write the former if they meant the latter.
- Sesse__ 3y agoThere are _tons_ of SQL queries that involve redundant and/or stupid things. A lot of them come from autogenerated queries where the user has nearly zero control over the actual SQL. An optimizer's job is to optimize that away, not reject them as “please be more efficient”. They are legal queries. All legal queries should be accepted and executed, no matter how silly one may consider them. As an aside, C compilers generally try to give warnings (not errors) on things like this, but need tons of heuristics to distinguish the “obviously wrong” cases from the “not obviously wrong” cases (e.g., those that arose from inlining and/or constant folding and/or preprocessor expansion and/or dead code removal).
- tremon 3y agoIt's not my style, but a previous team would always add a where-clause starting with WHERE 1=1 Because then any and all query predicates would be nicely aligned on the following lines, like this: WHERE 1=1 AND t1.col1 = 1 AND t2.col2 = 1 (Why they couldn't just lower the indent to read WHERE t1.col1 = 1 AND t2.col2 = 1 Is beyond me) Point being, don't assume that humans will never write an always-true predicate.
- lgas 3y agoPresumably it's to make copy/pasting or otherwise adding/removing entries easy. In the latter case you have to muck about with WHERE vs AND if you modify the top entry whereas if the immediate condition after WHERE is 1=1 then all the real conditions take on the identical form of 'AND x' which can be copy/pasted, reordered, etc without complications.
- throwaway201606 3y ago100% this: part of this crew. I have, over the course of my career, found that the teams that tend to do this are pure-SQL teams ie in DB developers who spend all their time writing - and more importantly - reading SQL. Doing this actually removes a lot of debugging and code-fixing friction
- porridgeraisin 3y agoIt's also useful to build a query string with where clauses appearing conditionally. where_clause = "where 1=1" if video.nsfw: where_clause += " and age > 18" if whatever: where_clause += " and whatever" query = "select * from user {where_clause}"
- ako 3y agoThat' a common way to implement (faceted) search where the search expressions are dynamic depending on the user input. With this style, if the user wants to search on one or more fields you simply add "and field = :value" and you don't have to check if it's the first or next search expression. The 1=1 doesn't hurt, as it will be removed by the query optimizer anyways.
- codeulike 3y agosuprisingly often in real situations when I just want to get the columns of a table with no data I have done select * from table where (1=0)