Menu
The following SQL is from Itzik Ben-Gan that is used to generate a numbers table. What does the order by (select null) part mean? Thanks.
DECLARE @number_of_numbers INT; SELECT @number_of_numbers = 100000; WITH a AS ( SELECT 1 AS i UNION ALL SELECT 1 ), b AS ( SELECT 1 AS i FROM a AS x , a AS y ), c AS ( SELECT 1 AS i FROM b AS x , b AS y ), d AS ( SELECT 1 AS i FROM c AS x , c AS y ), e AS ( SELECT 1 AS i FROM d AS x , d AS y ), f AS ( SELECT 1 AS i FROM e AS x , e AS y ), numbers AS ( SELECT TOP ( @number_of_numbers ) ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS number FROM f ) SELECT * FROM numbers; Thanks!
593
The 'select null' in the correlated subquery is a commonly used syntax because the column data returned is irrelevant. Instead the fact that a single row meets the join crieria is what is important with the EXISTS clause - this is what will trigger the inclusion of a row in the outer query.
If you sort a column with NULL values in ascending order, the NULLs will come first. Alternatively, if you add a DESC keyword to get a descending order, NULLs will appear last.
If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.
Description. The IS NULL condition is used in SQL to test for a NULL value. It returns TRUE if a NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
using ORDER BY NULL is a workaround that satifies the syntax requirement but does not actually change the order of the data. In effect it is an instruction to not order at all. N.B.: some (myself included) prefer to use SELECT 1 instead of SELECT NULL but there is no difference in effect.
The answer varies with the database type you use. The SQL standard offers NULLS FIRST / NULLS LAST options that change the sorting of NULL values when they’re added to ORDER BY. Unfortunately, not all databases support this standard.
Here’s an example: If you use the DESC keyword in ORDER BY to sort values in descending order, you’ll get NULL values at the top of the result table. Oracle treats NULLs the same way as PostgreSQL.
You cannot use it without one. SELECT NULL is a hack to shut up the error while not enforcing any particular order. In this case we don't need to enforce any order, so the fastest option is to use SELECT NULL. The optimizer sees through this trick, so it has no runtime cost (this claim is easily verified by looking at the execution plan).
ROW_NUMBER requires an ORDER BY clause syntactically. You cannot use it without one. SELECT NULL is a hack to shut up the error while not enforcing any particular order. In this case we don't need to enforce any order, so the fastest option is to use SELECT NULL.
The optimizer sees through this trick, so it has no runtime cost (this claim is easily verified by looking at the execution plan).
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
