SQL query: where true?

Question

I've found an SQL query in some code of the form:

select (list, of, fields) from table_name where true

The true is obviously a tautology so what is the point of it?

Answer 1

I've seen this idiom a few times, and even used it myself on occasion. By itself it is useless as you suspect; however it does make it easier to form more complex queries. Consider the case where a query form has a dozen different fields and the user may enter data in any combination of them to get a match - fields they leave blank are simply ignored.



The user application then must assemble these parameters into an SQL query. Consider a query with no where clause: the first field might be blank so nothing is changed there, similarly for the second. The user has entered something in the third field to match so you need to add a "where field3 = 'user data' " to the end of the query. The next non-blank field might be the sixth one and it needs to append an additional "and field6 = 'more user data' ".



Notice the pattern isn't uniform - the first condition to match begins with "where" and subsequent ones begin "and". That means the program must keep track of whether the query has already been modified, and insert "and" if it has or "where" otherwise. This isn't too difficult, but it adds complexity and dynamically generating SQL tends to be messy code at the best of times.



The "where true" avoids this by starting the conditions off straight away without modifying the search (at least if it is composed of conditions ANDed together). Then it is a simple matter to append "and (condition)" to the query at each stage without needing to track whether it is the first or a subsequent condition.

Answer 2

You are right that where true is pointless it may as well not have any where clause.



If you are looking at source code it's a bit strange that someone wrote that but if it's a log or something maybe the process asks for a where clause and a quick way to bypass that is just to supply true

Answer 3

The above is one (and in all probability the main spectacular) thank you to do it. yet another danger (albeit a much extra complicated one) could be to apply some thing like SUM(CASE whilst IsConfirmed = genuine a million ELSE 0 end) in a subquery.