Início / dba / Perguntas / 8504
Accepted
Richard
Asked: 2011-11-29 14:48:18 +0800 CST

Por que os NULLs são classificados primeiro?

  • 772

Por que é que quando temos um valor NULL em uma coluna e ordenamos pelo valor crescente, os NULLs são classificados primeiro?

select 1 as test
union all
select 2
union all
select NULL
union all
select 3
union all
select 4
order by test

resulta em

NULL
1
2
3
4

Eu continuo pensando que NULL significava "Indeterminante" ou possível "Desconhecido". Se isso for verdade, eles não classificariam por último, já que o valor pode ser maior que todos os outros valores? (Ou esta é uma opção de classificação em algum lugar?)

Estou no SQL Server 2008R2, mas suspeito que isso seja verdade em todos os SQL Servers e provavelmente em todos os RDBMSs.

sql-server database-theory

4 respostas

  1. Best Answer

    BOL : Um valor de NULL indica que o valor é desconhecido. Um valor de NULL é diferente de um valor vazio ou zero. Não há dois valores nulos iguais. As comparações entre dois valores nulos ou entre um NULL e qualquer outro valor retornam desconhecido porque o valor de cada NULL é desconhecido.

    NULL significa desconhecido. Nenhuma outra interpretação é válida.

    Se isso for verdade, eles não classificariam por último, já que o valor pode ser maior que todos os outros valores?

    Não há poderia ser . Não há valor potencial . Desconhecido é desconhecido é desconhecido.

    Quanto ao motivo pelo qual aparece primeiro, e não por último, isso não é atendido pelos padrões SQL publicados e, infelizmente, fica a critério do fornecedor do RDBMS:

    Wikipedia : O padrão SQL não define explicitamente uma ordem de classificação padrão para Nulos. Em vez disso, em sistemas em conformidade, os Nulos podem ser classificados antes ou depois de todos os valores de dados usando as cláusulas NULLS FIRST ou NULLS LAST da lista ORDER BY, respectivamente. No entanto, nem todos os fornecedores de DBMS implementam essa funcionalidade. Os fornecedores que não implementam essa funcionalidade podem especificar tratamentos diferentes para classificação Null no DBMS.

    • 20

  2. Você está correto que NULLpode significar 'Indeterminante' ou 'Desconhecido' ou 'Ainda não conhecido' ou 'Não se aplica'. Mas não há razão para colocar os Nulos em primeiro ou em último lugar. Se não soubermos os valores reais, então eles podem ser pequenos ou grandes.

    Eu acho que o padrão para determinar o comportamento desejado de Nulls durante a classificação é:

    ORDER BY 
    test NULLS LAST                      --- or NULLS FIRST for the opposite

    Infelizmente o SQL-Server ainda não adotou esta sintaxe. Se não estou errado PostgreSQL e Oracle tem.

    Uma solução:

    ORDER BY 
     CASE WHEN test IS NOT NULL 
            THEN 0 
          ELSE 1 
     END 
   , test

    Outra solução que precisa de ajuste dependendo do tipo de dados - mas não funcionará bem, pois não pode usar um índice em (test):

    ORDER BY 
    COALESCE(test, 2147483647)               --- if it's a 4-byte signed integer
    • 6

  3. Não sei por que é feito dessa maneira, mas, por definição, NULLS não pode ser comparado a não-NULLS, então eles precisam ir no início ou no final (a resposta de Mark cobre isso com muito mais detalhes).

    Para obter o comportamento desejado - Até onde eu sei, não há opção de classificação para colocar os nulos por último, então você precisa usar uma coluna computada para forçá-los por último. No entanto, no SQL Server você não pode ordenar por uma coluna computada ( CASE WHEN ...) quando seus dados contêm um operador de conjunto ( UNION ALL). Então:

    CREATE TABLE #sorttest(test int)
INSERT INTO #sorttest values(1)
INSERT INTO #sorttest values(5)
INSERT INTO #sorttest values(4)
INSERT INTO #sorttest values(NULL)
INSERT INTO #sorttest values(3)
INSERT INTO #sorttest values(2)
SELECT test
FROM #sorttest
ORDER BY CASE WHEN test IS NULL THEN 1 ELSE 0 END, test

DROP TABLE #sorttest

    Funcionará para classificar os nulos por último. Se você precisar usar UNION(ou EXCEPTou INTERSECTS) para gerar seu conjunto de dados, despeje seus dados em uma tabela temporária como acima.

    • 4

  4. Se você está lidando com números, você também pode usar

    ORDER BY -test DESC

    NULLsão os valores mais baixos possíveis, portanto, DESCos coloca no final. Enquanto isso, os valores não nulos têm o sinal invertido, portanto, DESCna verdade, é um ASCvalor real. Isso deve ser mais rápido CASEe suponho que o otimizador de consulta também possa usar índices na testcoluna.

    • 0

relate perguntas