Jak działają instrukcje SQL EXISTS?

Próbuję nauczyć się języka SQL i mam trudności ze zrozumieniem instrukcji ISTNIEJE. Natknąłem się na ten cytat o „istnieje” i czegoś nie rozumiem:

Używając operatora exist, twoje podzapytanie może zwrócić zero, jeden lub wiele wierszy, a warunek po prostu sprawdza, czy podzapytanie zwróciło jakieś wiersze. Jeśli spojrzysz na klauzulę select w podzapytaniu, zobaczysz, że składa się ona z jednego literału (1); ponieważ warunek w zapytaniu zawierającym musi tylko wiedzieć, ile wierszy zostało zwróconych, rzeczywiste dane zwrócone przez podzapytanie są nieistotne.

Nie rozumiem, skąd zapytanie zewnętrzne wie, który wiersz jest sprawdzane przez podzapytanie? Na przykład:

SELECT *
  FROM suppliers
 WHERE EXISTS (select *
                 from orders
                where suppliers.supplier_id = orders.supplier_id);

Rozumiem, że jeśli identyfikator dostawcy i tabeli zamówień będzie się zgadzać, podzapytanie zwróci wartość true i zostaną wyprowadzone wszystkie kolumny z pasującego wiersza w tabeli dostawców. Nie rozumiem, w jaki sposób podzapytanie informuje, który konkretny wiersz (powiedzmy wiersz z identyfikatorem dostawcy 25) powinien zostać wydrukowany, jeśli zwracana jest tylko prawda lub fałsz.

Wydaje mi się, że nie ma związku między zapytaniem zewnętrznym a podzapytaniem.

Pomyśl o tym w ten sposób:

Dla „każdego” wiersza z Suppliers, sprawdź, czy „istnieje” wiersz w Ordertabeli, który spełnia warunek Suppliers.supplier_id(pochodzi z bieżącego „wiersza” zapytania Outer) = Orders.supplier_id. Gdy znajdziesz pierwszy pasujący wiersz, zatrzymaj się w tym miejscu - WHERE EXISTSzostał spełniony.

Magiczne połączenie między zapytaniem zewnętrznym a podzapytaniem polega na tym, że Supplier_idjest ono przekazywane z zapytania zewnętrznego do podzapytania dla każdego ocenianego wiersza.

Inaczej mówiąc, podzapytanie jest wykonywane dla każdego wiersza tabeli zapytania zewnętrznego.

To NIE jest tak, że podzapytanie jest wykonywane w całości i pobiera „prawda / fałsz”, a następnie próbuje dopasować ten warunek „prawda / fałsz” z zapytaniem zewnętrznym.

Wydaje mi się, że nie ma związku między zapytaniem zewnętrznym a podzapytaniem.

Jak myślisz, co robi klauzula WHERE w przykładzie EXISTS? Jak doszedłeś do takiego wniosku, skoro odniesienia SUPPLIERS nie ma w klauzulach FROM ani JOIN w klauzuli EXISTS?

ISTNIEJE przyjmuje wartości PRAWDA / FAŁSZ i wychodzi jako PRAWDA przy pierwszym dopasowaniu kryteriów - dlatego może być szybsze niż IN. Należy również pamiętać, że klauzula SELECT w EXISTS jest ignorowana - IE:

SELECT s.*
  FROM SUPPLIERS s
 WHERE EXISTS (SELECT 1/0
                 FROM ORDERS o
                WHERE o.supplier_id = s.supplier_id)

... powinien osiągnąć błąd dzielenia przez zero, ale tak się nie stanie. Klauzula WHERE jest najważniejszym elementem klauzuli EXISTS.

Należy również pamiętać, że JOIN nie zastępuje bezpośrednio EXISTS, ponieważ będą zduplikowane rekordy nadrzędne, jeśli istnieje więcej niż jeden rekord podrzędny powiązany z nadrzędnym.

Można produkować identyczne wyniki stosując albo JOIN, EXISTS, IN, lub INTERSECT:

SELECT s.supplier_id
FROM suppliers s
INNER JOIN (SELECT DISTINCT o.supplier_id FROM orders o) o
    ON o.supplier_id = s.supplier_id

SELECT s.supplier_id
FROM suppliers s
WHERE EXISTS (SELECT * FROM orders o WHERE o.supplier_id = s.supplier_id)

SELECT s.supplier_id 
FROM suppliers s 
WHERE s.supplier_id IN (SELECT o.supplier_id FROM orders o)

SELECT s.supplier_id
FROM suppliers s
INTERSECT
SELECT o.supplier_id
FROM orders o

Gdybyś miał klauzulę where, która wyglądała tak:

WHERE id in (25,26,27) -- and so on

możesz łatwo zrozumieć, dlaczego niektóre wiersze są zwracane, a inne nie.

Kiedy klauzula where wygląda następująco:

WHERE EXISTS (select * from orders where suppliers.supplier_id = orders.supplier_id);

oznacza to po prostu: zwraca wiersze, które mają istniejący rekord w tabeli zamówień o tym samym identyfikatorze.

To bardzo dobre pytanie, dlatego postanowiłem napisać bardzo szczegółowy artykuł na ten temat na swoim blogu.

Model tabeli bazy danych

Załóżmy, że w naszej bazie danych mamy następujące dwie tabele, które tworzą relację jeden-do-wielu.

studentStół jest rodzicem, a student_gradeto tabela dziecko, ponieważ ma to student_id kolumny klucz obcy odniesienia do identyfikatora kolumny klucza podstawowego w tabeli studentów.

student tableZawiera następujące dwa rekordy:

| id | first_name | last_name | admission_score |
|----|------------|-----------|-----------------|
| 1  | Alice      | Smith     | 8.95            |
| 2  | Bob        | Johnson   | 8.75            |

, A student_gradetabela przechowuje klas otrzymali uczniowie:

| id | class_name | grade | student_id |
|----|------------|-------|------------|
| 1  | Math       | 10    | 1          |
| 2  | Math       | 9.5   | 1          |
| 3  | Math       | 9.75  | 1          |
| 4  | Science    | 9.5   | 1          |
| 5  | Science    | 9     | 1          |
| 6  | Science    | 9.25  | 1          |
| 7  | Math       | 8.5   | 2          |
| 8  | Math       | 9.5   | 2          |
| 9  | Math       | 9     | 2          |
| 10 | Science    | 10    | 2          |
| 11 | Science    | 9.4   | 2          |

SQL ISTNIEJE

Powiedzmy, że chcemy zebrać wszystkich uczniów, którzy otrzymali 10 ocen z matematyki.

Jeśli interesuje nas tylko identyfikator ucznia, możemy uruchomić zapytanie takie jak to:

SELECT
    student_grade.student_id
FROM
    student_grade
WHERE
    student_grade.grade = 10 AND
    student_grade.class_name = 'Math'
ORDER BY
    student_grade.student_id

Ale aplikacja jest zainteresowana wyświetlaniem pełnej nazwy a student, a nie tylko identyfikatora, więc potrzebujemy również informacji z studenttabeli.

Aby odfiltrować studentrekordy, które mają ocenę 10 z matematyki, możemy użyć operatora SQL EXISTS, na przykład:

SELECT
    id, first_name, last_name
FROM
    student
WHERE EXISTS (
    SELECT 1
    FROM
        student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade = 10 AND
        student_grade.class_name = 'Math'
)
ORDER BY id

Po uruchomieniu powyższego zapytania widzimy, że wybrany jest tylko wiersz Alice:

| id | first_name | last_name |
|----|------------|-----------|
| 1  | Alice      | Smith     |

Zapytanie zewnętrzne wybiera studentkolumny wierszy, które chcemy zwrócić klientowi. Jednak klauzula WHERE używa operatora EXISTS z powiązanym podzapytaniem wewnętrznym.

Operator EXISTS zwraca wartość true, jeśli podzapytanie zwróci co najmniej jeden rekord, lub false, jeśli nie wybrano żadnego wiersza. Silnik bazy danych nie musi całkowicie uruchamiać podzapytania. Jeśli dopasowany zostanie pojedynczy rekord, operator EXISTS zwraca wartość true i wybrany jest powiązany z nim inny wiersz zapytania.

Podzapytanie wewnętrzne jest skorelowane, ponieważ kolumna student_id student_gradetabeli jest dopasowana do kolumny id zewnętrznej tabeli uczniów.

ISTNIEJE oznacza, że ​​podzapytanie zwraca co najmniej jeden wiersz, to naprawdę wszystko. W takim przypadku jest to podzapytanie skorelowane, ponieważ sprawdza dostawca_id tabeli zewnętrznej z identyfikatorem dostawcy w tabeli wewnętrznej. To zapytanie mówi w efekcie:

WYBIERZ wszystkich dostawców Dla każdego ID dostawcy sprawdź, czy istnieje zamówienie dla tego dostawcy Jeśli dostawca nie jest obecny w tabeli zamówień, usuń dostawcę z wyników POWRÓT wszystkich dostawców, którzy mają odpowiednie wiersze w tabeli zamówień

Możesz zrobić to samo w tym przypadku z INNER JOIN.

SELECT suppliers.* 
  FROM suppliers 
 INNER 
  JOIN orders 
    ON suppliers.supplier_id = orders.supplier_id;

Komentarz kucyków jest poprawny. Musisz grupować za pomocą tego połączenia lub wybierać różne w zależności od potrzebnych danych.

To, co opisujesz, to tak zwane zapytanie ze skorelowanym podzapytaniem .

(Ogólnie) jest to coś, czego powinieneś starać się unikać, pisząc zapytanie za pomocą złączenia:

SELECT suppliers.* 
FROM suppliers 
JOIN orders USING supplier_id
GROUP BY suppliers.supplier_id

Ponieważ w przeciwnym razie podzapytanie zostanie wykonane dla każdego wiersza w zapytaniu zewnętrznym.