ISTNIEJE (WYBIERZ 1…) vs ISTNIEJE (WYBIERZ *…) Jedno czy drugie?

Ilekroć muszę sprawdzić, czy istnieje jakiś wiersz w tabeli, zwykle piszę taki warunek:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT *  -- This is what I normally write
          FROM another_table
         WHERE another_table.b = a_table.b
       )

Niektóre inne osoby piszą to tak:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT 1   --- This nice '1' is what I have seen other people use
          FROM another_table
         WHERE another_table.b = a_table.b
       )

Gdy warunek jest NOT EXISTSzamiast EXISTS: W niektórych przypadkach mogę napisać go z LEFT JOINdodatkowym warunkiem (czasami nazywanym anty - dołączeniem ):

SELECT a, b, c
  FROM a_table
       LEFT JOIN another_table ON another_table.b = a_table.b
 WHERE another_table.primary_key IS NULL

Staram się tego unikać, ponieważ uważam, że znaczenie jest mniej jasne, szczególnie gdy to, co jest twoje, primary_keynie jest tak oczywiste, lub gdy twój klucz podstawowy lub warunek łączenia jest wielokolumnowy (i możesz łatwo zapomnieć o jednej z kolumn). Czasami jednak utrzymujesz kod napisany przez kogoś innego ... i to jest właśnie tam.

1. Czy jest jakaś różnica (inna niż styl) do użycia SELECT 1zamiast SELECT *?
   Czy istnieje przypadek narożny, w którym nie zachowuje się tak samo?

2. Chociaż to, co napisałem, to standardowy SQL (AFAIK): Czy istnieje taka różnica dla różnych baz danych / starszych wersji?

3. Czy pisanie antijoin ma jakąś przewagę?
   Czy współcześni planiści / optymaliści traktują to inaczej niż NOT EXISTSklauzula?

Nie, nie ma żadnej różnicy w wydajności pomiędzy (NOT) EXISTS (SELECT 1 ...)i (NOT) EXISTS (SELECT * ...)we wszystkich głównych DBMS. Często też widziałem, (NOT) EXISTS (SELECT NULL ...)że jestem używany.

W niektórych można nawet pisać, (NOT) EXISTS (SELECT 1/0 ...)a wynik jest taki sam - bez żadnego błędu (dzielenie przez zero), co dowodzi, że wyrażenie nie jest nawet oceniane.

Jeśli chodzi o LEFT JOIN / IS NULLmetodę antijoin, korekta: jest to równoważne z NOT EXISTS (SELECT ...).

W tym przypadku NOT EXISTSvsLEFT JOIN / IS NULL, możesz otrzymać różne plany wykonania. Na przykład w MySQL i głównie w starszych wersjach (wcześniejszych niż 5.7) plany byłyby dość podobne, ale nie identyczne. Optymalizatory innych DBMS (SQL Server, Oracle, Postgres, DB2) są - o ile wiem - mniej więcej w stanie przepisać te dwie metody i rozważyć te same plany dla obu. Nadal nie ma takiej gwarancji, a podczas optymalizacji warto sprawdzać plany z różnych równoważnych przepisań, ponieważ mogą wystąpić przypadki, że każdy optymalizator nie przepisuje (np. Złożone zapytania, wiele sprzężeń i / lub tabele pochodne / podkwerendy wewnątrz podkwerendy, w których warunki z wielu tabel, kolumny złożone stosowane w warunkach łączenia) lub wybory i plany optymalizatora różnią się w zależności od dostępnych indeksów, ustawień itp.

Należy również pamiętać, że USINGnie można go używać we wszystkich DBMS (na przykład SQL Server). Częściej JOIN ... ONdziała wszędzie.
A kolumny muszą być poprzedzone nazwą tabeli / aliasem, SELECTaby uniknąć błędów / dwuznaczności, gdy mamy połączenia.
Zazwyczaj wolę również umieścić połączoną kolumnę w IS NULLsprawdzeniu (chociaż PK lub dowolna niepustowa kolumna byłaby OK, może być użyteczna dla wydajności, gdy plan LEFT JOINużycia indeksu nieklastrowego):

SELECT a_table.a, a_table.b, a_table.c
  FROM a_table
       LEFT JOIN another_table 
           ON another_table.b = a_table.b
 WHERE another_table.b IS NULL ;

Istnieje również trzecia metoda dla antijoins, wykorzystująca, NOT INale ma inną semantykę (i wyniki!), Jeśli kolumna wewnętrznej tabeli jest zerowa. Można go jednak wykorzystać, wykluczając wiersze z NULL, dzięki czemu zapytanie jest równoważne z poprzednimi 2 wersjami:

SELECT a, b, c
  FROM a_table
 WHERE a_table.b NOT IN 
       (SELECT another_table.b
          FROM another_table
         WHERE another_table.b IS NOT NULL
       ) ;

Zwykle daje to również podobne plany w większości DBMS.

Jest jeszcze jedna kategoria przypadkach SELECT 1i SELECT *nie są wymienne - a dokładniej, jeden zawsze będzie akceptowane w tych przypadkach, gdy inne przeważnie nie będzie.

Mówię o przypadkach, w których musisz sprawdzić, czy istnieją wiersze zgrupowanego zestawu. Jeśli tabela Tma kolumny C1i C2sprawdzasz, czy istnieją grupy wierszy pasujące do określonego warunku, możesz użyć SELECT 1tego w następujący sposób:

EXISTS
(
  SELECT
    1
  FROM
    T
  GROUP BY
    C1
  HAVING
    AGG(C2) = SomeValue
)

ale nie możesz używać SELECT *w ten sam sposób.

To jest tylko aspekt składniowy. Jeśli obie opcje są akceptowane składniowo, najprawdopodobniej nie będzie żadnej różnicy pod względem wydajności lub zwracanych wyników, jak wyjaśniono w innej odpowiedzi .

Dodatkowe uwagi po komentarzach

Wydaje się, że niewiele produktów bazodanowych faktycznie obsługuje to rozróżnienie. Produkty takie jak SQL Server, Oracle, MySQL i SQLite z radością przyjmą SELECT *powyższe zapytanie bez żadnych błędów, co prawdopodobnie oznacza, że ​​traktują ISTNIEJE SELECTw specjalny sposób.

PostgreSQL to jeden RDBMS, w którym SELECT *może się nie powieść, ale może nadal działać w niektórych przypadkach. W szczególności, jeśli grupujesz według PK, SELECT *będzie działać dobrze, w przeciwnym razie nie powiedzie się komunikat:

BŁĄD: kolumna „T.C2” musi pojawić się w klauzuli GROUP BY lub być użyta w funkcji agregującej

Prawdopodobnie interesującym sposobem ponownego napisania EXISTSklauzuli, która daje czystsze i być może mniej mylące zapytanie, przynajmniej w SQL Server byłoby:

SELECT a, b, c
  FROM a_table
 WHERE b = ANY
       (
          SELECT b
          FROM another_table
       );

Wersja antyprzyłączona wyglądałaby następująco:

SELECT a, b, c
  FROM a_table
 WHERE b <> ALL
       (
          SELECT b
          FROM another_table
       );

Oba są zazwyczaj zoptymalizowane do tego samego planu co WHERE EXISTSlub WHERE NOT EXISTS, ale intencja jest jednoznaczna i nie masz „dziwnego” 1lub *.

Co ciekawe, problemy z zerowym sprawdzaniem NOT IN (...)są problematyczne <> ALL (...), podczas gdy NOT EXISTS (...)nie cierpią z powodu tego problemu. Rozważ następujące dwie tabele z kolumną zerowalną:

IF OBJECT_ID('tempdb..#t') IS NOT NULL
BEGIN
    DROP TABLE #t;
END;
CREATE TABLE #t 
(
    ID INT NOT NULL IDENTITY(1,1)
    , SomeValue INT NULL
);

IF OBJECT_ID('tempdb..#s') IS NOT NULL
BEGIN
    DROP TABLE #s;
END;
CREATE TABLE #s 
(
    ID INT NOT NULL IDENTITY(1,1)
    , SomeValue INT NULL
);

Dodamy trochę danych do obu, z niektórymi pasującymi wierszami, a niektóre, które nie:

INSERT INTO #t (SomeValue) VALUES (1);
INSERT INTO #t (SomeValue) VALUES (2);
INSERT INTO #t (SomeValue) VALUES (3);
INSERT INTO #t (SomeValue) VALUES (NULL);

SELECT *
FROM #t;

+ -------- + ----------- +
| ID | SomeValue |
+ -------- + ----------- +
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | NULL |
+ -------- + ----------- +

INSERT INTO #s (SomeValue) VALUES (1);
INSERT INTO #s (SomeValue) VALUES (2);
INSERT INTO #s (SomeValue) VALUES (NULL);
INSERT INTO #s (SomeValue) VALUES (4);

SELECT *
FROM #s;

+ -------- + ----------- +
| ID | SomeValue |
+ -------- + ----------- +
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
| 4 | 4 |
+ -------- + ----------- +

NOT IN (...)Zapytania:

SELECT *
FROM #t 
WHERE #t.SomeValue NOT IN (
    SELECT #s.SomeValue
    FROM #s 
    );

Ma następujący plan:

Zapytanie nie zwraca wierszy, ponieważ wartości NULL uniemożliwiają potwierdzenie równości.

To zapytanie z <> ALL (...)pokazuje ten sam plan i nie zwraca żadnych wierszy:

SELECT *
FROM #t 
WHERE #t.SomeValue <> ALL (
    SELECT #s.SomeValue
    FROM #s 
    );

Wariant przy użyciu NOT EXISTS (...), pokazuje nieco inny kształt planu i zwraca wiersze:

SELECT *
FROM #t 
WHERE NOT EXISTS (
    SELECT 1
    FROM #s 
    WHERE #s.SomeValue = #t.SomeValue
    );

Plan:

Wyniki tego zapytania:

+ -------- + ----------- +
| ID | SomeValue |
+ -------- + ----------- +
| 3 | 3 |
| 4 | NULL |
+ -------- + ----------- +

To sprawia, że ​​używanie <> ALL (...)jest równie podatne na problematyczne wyniki, jak NOT IN (...).

Należy zrobić „dowód”, że są one identyczne (w MySQL)

EXPLAIN EXTENDED
    SELECT EXISTS ( SELECT * ... ) AS x;
SHOW WARNINGS;

następnie powtórz za pomocą SELECT 1. W obu przypadkach „rozszerzone” wyjście pokazuje, że zostało przekształcone SELECT 1.

Podobnie COUNT(*)zamienia się w COUNT(0).

Kolejna rzecz do zapamiętania: W ostatnich wersjach wprowadzono ulepszenia optymalizacji. Może warto porównać EXISTSz anty-złączeniami. Twoja wersja może lepiej sobie radzić z jedną z drugą.

W niektórych bazach danych ta optymalizacja jeszcze nie działa. Podobnie jak na przykład w PostgreSQL Począwszy od wersji 9.6, to się nie powiedzie.

SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT *
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  HAVING count(*) > 1
);

I to się powiedzie.

SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT 1  -- This changed from the first query
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  HAVING count(*) > 1
);

Nie udaje się, ponieważ następujące błędy zawodzą, ale nadal oznacza to, że istnieje różnica.

SELECT *
FROM ( VALUES (1),(1) ) AS t(x)
HAVING count(*) > 1;

Więcej informacji o tym dziwactwie i naruszeniu specyfikacji można znaleźć w mojej odpowiedzi na pytanie: czy specyfikacja SQL wymaga GROUP BY w EXISTS ()

Zawsze używałem select top 1 'x'(SQL Server)

Teoretycznie select top 1 'x'byłoby bardziej wydajne select *, ponieważ pierwszy byłby kompletny po wybraniu stałej dotyczącej istnienia szeregu kwalifikacyjnego, podczas gdy drugi wybrałby wszystko.

JEDNAK, chociaż bardzo wcześnie mogło być istotne, optymalizacja sprawiła, że ​​różnica nie była istotna w prawdopodobnie wszystkich głównych RDBS.

IF EXISTS(SELECT TOP(1) 1 FROMjest lepszym nawykiem długoterminowym i na różnych platformach, ponieważ nie musisz nawet martwić się o to, jak dobra lub zła jest twoja obecna platforma / wersja; a SQL zmierza od TOP nparametryzacji TOP(n). To powinna być umiejętność jednorazowa.