Início / dba / Perguntas / 56840
Accepted
Tom Ellis
Asked: 2014-01-16 09:15:15 +0800 CST

Posso fornecer um padrão para uma junção externa esquerda?

  • 772

Suponha que eu tenha as tabelas a (com coluna a1) e b (com colunas b1 e b2) e execute uma junção externa esquerda

SELECT *
FROM a LEFT OUTER JOIN b
ON a.a1 = b.b1

Então b1 e b2 serão NULL onde um valor de a1 não tem valor correspondente de b1.

Posso fornecer um valor padrão para b2, em vez de NULL? Observe que COALESCE não funcionará aqui, porque não quero que o valor padrão substitua NULLs em potencial em b2, onde há um valor de b1 correspondente a a1.

Ou seja, com a e b como

CREATE TABLE a (a1)
  AS VALUES (1),
            (2),
            (3) ;

CREATE TABLE b (b1,b2)
  AS VALUES (1, 10),
            (3, null) ;


a1     b1 | b2
---    --------
 1      1 | 10
 2      3 | NULL
 3

e um padrão para b2 de, digamos, 100, quero obter o resultado

a1 | b1   | b2
---------------
1  |  1   | 10
2  | NULL | 100
3  |  3   | NULL

Neste caso simples, eu poderia fazê-lo "manualmente", verificando se b1 é NULL na saída. Essa é a melhor opção em geral, ou existe uma maneira mais padrão e mais limpa?

postgresql join

5 respostas

  1. Best Answer
    SELECT a.a1,b.b1,  
    CASE WHEN b.b1 is NULL THEN 5 ELSE b.b2 END AS b2  
FROM a LEFT OUTER JOIN b  
ON a.a1 = b.b1
    • 39

  2. Acho COALESCE muito útil nesse caso. Ele retornará o primeiro valor não NULL de uma lista:

    SELECT
 a.a1,
 b.b1,
 COALESCE (b.b2, 100) AS b2
FROM a
LEFT OUTER JOIN b
  ON (a.a1 = b.b1);

    Depois que @ypercube identificou corretamente um problema em que minha resposta não correspondia à pergunta. aqui está a correção dele:

    SELECT 
  a.a1, 
  b.b1, 
  COALESCE(b.b2, d.b2) AS b2   
FROM a LEFT JOIN b ON a.a1 = b.b1 
LEFT JOIN (SELECT 100 AS b2) AS d ON b.b1 IS NULL;
    • 17

  3. A resposta original a essa pergunta não foi explicada, então vamos tentar outra vez.

    Usando uma CASEexpressão

    Usando esse método, exploramos que temos outro valor em uma coluna diferente queIS NOT NULL , nesse caso, b.b1se esse valor for nulo, saberemos que a junção falhou.

    SELECT
  a.a1,
  b.b1,  
  CASE WHEN b.b1 is NULL THEN 100 ELSE b.b2 END AS b2  
FROM a
LEFT OUTER JOIN b  
  ON (a.a1 = b.b1);

    Isso funcionará totalmente e gerará exatamente a coisa que você deseja.

    Usando um sub-SELECT

    Não use este método, é uma ideia de construção. Continue lendo.

    Se não tivermos nenhuma NOT NULLcoluna que possamos explorar assim, precisamos de algo para criar uma coluna que possa funcionar dessa maneira para nós ...

    SELECT
  a.a1,
  b.b1,  
  CASE WHEN b.cond IS NULL THEN 100 ELSE b.b2 END AS b2  
FROM a
LEFT OUTER JOIN (
  SELECT true AS cond, b.*
  FROM b
) AS b
  ON (a.a1 = b.b1);

    Usando uma comparação de linhas

    Ainda mais fácil forçar um valor falso para o qual podemos comparar é comparar a linha. No PostgreSQL, a linha tem um valor pelo nome da tabela. Por exemplo, SELECT foo FROM fooretorna uma linha do tipo foo(que é um tipo de linha), da tabela foo. Aqui testamos para ver se essa ROW é nula. Isso funcionará desde que cada coluna IS NOT NULL. E, se todas as colunas IS NULLda sua tabela, você está apenas trollando.

    SELECT
  a.a1,
  b.b1,  
  CASE WHEN b IS NULL THEN 100 ELSE b.b2 END AS b2  
FROM a
LEFT OUTER JOIN b
  ON (a.a1 = b.b1);
    • 3

  4. Como alternativa aos valores das colunas, pode-se querer usar uma linha padrão nas linhas não correspondentes

    Usando o dialeto tsql com base no comentário: Adicionei uma tag para indicar que aceitaria uma resposta específica do Postgres. Ainda assim, o SQL padrão seria o preferido, se possível .

    Dados de teste

    SELECT *
INTO #a
FROM (VALUES (1)
           , (2)
           , (3)) AS x(a1);

SELECT *
INTO #b
FROM (VALUES (1, 10)
           , (3, NULL)
     ) AS x(b1, b2);

    Consulta básica

    SELECT *
FROM #a JOIN #b ON #a.a1 = #b.b1 -- all matches
UNION
SELECT #a.a1, NULL, 100 -- default row
FROM #a LEFT JOIN #b ON #a.a1 = #b.b1 WHERE b1 IS NULL -- where a1 <> b1;

    Resultado básico

    |a1|b1|b2 |
-----------
| 1| 1| 10|
| 2|  |100|
| 3| 3|   |

    Você pode usar essa abordagem para uma abordagem mais baseada em conjuntos, tratando os padrões como substitutos para a diferença nos dois conjuntos.

    Aqui está um exemplo mais avançado em que os ids pares/ímpares de têm diferentes linhas apadrão .b

    Consulta avançada

    INSERT INTO #a
SELECT *
FROM (VALUES (5)) AS x(a1);

SELECT *
FROM #a
     JOIN #b ON #a.a1 = #b.b1 -- all matches
UNION
SELECT a1, NULL, val
FROM #a
     LEFT JOIN #b ON #a.a1 = #b.b1
     JOIN (
    SELECT *
    FROM (VALUES (0, 100) -- even default
               , (1, 99) -- odd default
         ) AS x(rem, val)
  ) t ON a1 % 2 = t.rem
WHERE b1 IS NULL -- where a1 <> b1;

    Resultado avançado

    |a1|b1|b2 |
-----------
| 1| 1| 10|
| 2|  |100|
| 3| 3|   |
| 5|  | 99|
    • 2

  5. O OP pediu uma alternativa ao uso do método case / coalesce / ifnull típico para substituir valores nulos, especificando um 'valor padrão' diferente para junções à esquerda em vez de nulo. Esta é uma solicitação válida, pois pode ser complicada se o campo for usado repetidamente ou em cálculos complexos na consulta ou se você quiser diferenciar entre o sinal 'linha não existe na tabela unida' e o resultado 'linha existe, mas esse campo é nulo' . A resposta correta aqui é "não, esse recurso não existe".

    • -1

relate perguntas