Jak wybrać wiersze bez pasującego wpisu w innej tabeli?

Wykonuję pewne prace konserwacyjne nad aplikacją bazodanową i odkryłem, że joy of joys, mimo że wartości z jednej tabeli są używane w stylu kluczy obcych, nie ma ograniczeń dla kluczy obcych na tabelach.

Staram się dodać do tych kolumn ograniczenia FK, ale stwierdzam, że, ponieważ jest już cały ładunek złych danych w tabelach z poprzednich błędów, które zostały naiwnie poprawione, muszę znaleźć wiersze, które nie pasują do innych tabeli, a następnie je usunąć.

Znalazłem kilka przykładów tego rodzaju zapytań w sieci, ale wszystkie wydają się dostarczać przykładów, a nie wyjaśnień i nie rozumiem, dlaczego działają.

Czy ktoś może mi wyjaśnić, jak skonstruować zapytanie, które zwraca wszystkie wiersze bez dopasowania w innej tabeli i co robi, tak, że mogę te zapytania zrobić sam, zamiast przybiegać do SO dla każdej tabeli w tym bałagan , który nie ma ograniczeń FK?

Author: Kara, 2010-11-02
Source

7 answers

Oto proste zapytanie: 

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

Kluczowe punkty to:

  1. LEFT JOIN zwraca wszystkie wiersze z Table1, niezależnie od tego, czy w Table2 jest pasujący wiersz.

  2. Klauzula WHERE t2.ID IS NULL; spowoduje to ograniczenie wyników zwracanych tylko do tych wierszy, w których ID zwrócone z Table2 jest null - innymi słowy nie ma rekordu w Table2 dla tego konkretnego ID z Table1. Table2.ID zostanie zwrócona jako NULL dla wszystkich rekordów z Table1 gdzie ID nie jest dopasowane w Table2.

 423
Author: AdaTheDev,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-03-16 12:59:21

Użyłbym EXISTS wyrażenie ponieważ jest bardziej wydajne, możesz dokładniej wybrać wiersze, które chcesz połączyć, w przypadku LEFT JOIN musisz wziąć wszystko, co znajduje się w tabeli joined. Jego sprawność jest prawdopodobnie taka sama jak w przypadku {[2] } z testem null.

SELECT t1.ID
FROM Table1 t1
WHERE NOT EXISTS (SELECT t2.ID FROM Table2 t2 WHERE t1.ID = t2.ID)
 33
Author: Ondrej Bozek,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-18 12:48:18 
SELECT id FROM table1 WHERE foreign_key_id_column NOT IN (SELECT id FROM table2)

Tabela 1 ma kolumnę, do której chcesz dodać ograniczenie klucza obcego, ale wartości w foreign_key_id_column nie pasują do id w tabeli 2.

  1. początkowe select wyświetla identyfikatory z table1. będą to wiersze, które chcemy usunąć.
  2. klauzula "not in" w instrukcji where ogranicza zapytanie tylko do wierszy, w których wartość w foreign_key_id_column nie znajduje się na liście identyfikatorów tabeli 2.
  3. instrukcja select w nawiasie otrzyma lista wszystkich identyfikatorów znajdujących się w tabeli 2.
 7
Author: Theo Voss,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-05-31 17:36:39

Gdzie T2 jest tabelą, do której dodajesz ograniczenie: 

SELECT *
FROM T2
WHERE constrain_field NOT
IN (
    SELECT DISTINCT t.constrain_field
    FROM T2 
    INNER JOIN T1 t
    USING ( constrain_field )
)

I usunąć wyniki.

 6
Author: Karel,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-04-16 18:41:44

Niech mamy następujące 2 tabele (wynagrodzenie i pracownik) Tutaj wpisz opis obrazka

Teraz chcę te dane z tabeli pracowników, które nie są w pensji. Możemy to zrobić na 3 sposoby -

  1. Korzystanie z połączenia wewnętrznego

    Select * from employee where id not in (select e.id od pracownika e wewnętrzne Dołącz wynagrodzenie s na e.id=s.id)

Tutaj wpisz opis obrazka

  1. Korzystanie z LEFT outer join

    Select * from employee e left outer join pensja s na e.id=s.id gdzie s.id is null

Tutaj wpisz opis obrazka

  1. Korzystanie Z Pełnego Połączenia

    Select * from employee e full outer join pensja s on e.id=s.id gdzie e.id Nie w (Wybierz id z pensji)

Tutaj wpisz opis obrazka

 1
Author: Debendra Dash,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-14 15:32:55

Nie wiedziałem, który jest zoptymalizowany (w porównaniu do @AdaTheDev ) ale ten wydaje się być szybszy, gdy używam (przynajmniej dla mnie)

SELECT id FROM table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2

Jeśli chcesz uzyskać inny konkretny atrybut, możesz użyć:

SELECT COUNT(*) FROM table_1 where id in (SELECT id FROM table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2);

 0
Author: Jerin K. John,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-25 06:59:38

Jak wybrać wiersze bez pasującego wpisu w obu tabelach?


    select * from [dbo].[EmppDetails] e
     right join [Employee].[Gender] d on e.Gid=d.Gid
    where e.Gid is Null

    union 
    select * from [dbo].[EmppDetails] e
     left join [Employee].[Gender] d on e.Gid=d.Gid
    where d.Gid is Null
 -2
Author: user6341745,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-06 05:45:55