4 ms·
Not really a trick in the spirit of the blog post, but I often catch people out with this, after telling them that it's legal to divide by zero in SQL: SEL
by ilitirit 10y ago
Not really a trick in the spirit of the blog post, but I often catch people out with this, after telling them that it's legal to divide by zero in SQL:
SELECT *
FROM ThisTable
WHERE EXISTS
(
SELECT 1/0
FROM OtherTable
)
- deleted 10y ago[deleted]
- mistermann 10y agoWhat's this doing???
- ilitirit 10y agoIt's a query with an existence constraint, meaning that the query will only return data if at least 1 value is returned by the EXISTS predicate. In this case, you should get back all the data from ThisTable as long as there's a row in OtherTable. But that's not the "tricky" bit. I'm using a feature of SQL that ignores the values in the SELECT clause when used as part of the EXISTS predicate. In the past, people used to type in the EXISTS predicate SELECT 1... because it was (wrongfully[1]) assumed to be faster than SELECT <column(s)>. This query proves that the query engine does not attempt to compute the SELECTed values. If it didn't, it would fail with a division by zero error. [1] See Chapter 7.9: If the <select list> "*" is simply contained in a <subquery> that is immediately contained in an <exists predicate>, then the <select list> is equivalent to a <value expression> that is an arbitrary <literal>. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
- chris_wot 10y agoAs an aside, quite frankly I could never get EXISTS and NOT EXISTS to work in Qlikview. I honest wish Qlikview adopted a more SQL-like exists clause. (P.S. I used to do SELECT 1 because it was shorthand... Funny how people thought it was a magic speed up!)