SQL - znajdź rekordy z jednej tabeli, które nie istnieją w innej

310

Mam następujące dwie tabele SQL (w MySQL):

Phone_book
+----+------+--------------+
| id | name | phone_number |
+----+------+--------------+
| 1  | John | 111111111111 |
+----+------+--------------+
| 2  | Jane | 222222222222 |
+----+------+--------------+

Call
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 1  | 0945 | 111111111111 |
+----+------+--------------+
| 2  | 0950 | 222222222222 |
+----+------+--------------+
| 3  | 1045 | 333333333333 |
+----+------+--------------+

Jak sprawdzić, które połączenia zostały wykonane przez osoby, których phone_numbernie ma w Phone_book? Pożądanym wynikiem byłoby:

Call
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 3  | 1045 | 333333333333 |
+----+------+--------------+

Każda pomoc będzie mile widziana.

sql mysql

— Philip Morton
źródło

438

Można to zrobić na kilka różnych sposobów, z różną wydajnością, w zależności od tego, jak dobry jest optymalizator zapytań, oraz od względnego rozmiaru dwóch tabel:

To jest najkrótsza wypowiedź i może być najszybsza, jeśli książka telefoniczna jest bardzo krótka:

SELECT  *
FROM    Call
WHERE   phone_number NOT IN (SELECT phone_number FROM Phone_book)

alternatywnie (dzięki Alterlife )

SELECT *
FROM   Call
WHERE  NOT EXISTS
  (SELECT *
   FROM   Phone_book
   WHERE  Phone_book.phone_number = Call.phone_number)

lub (dzięki WOPR)

SELECT * 
FROM   Call
LEFT OUTER JOIN Phone_Book
  ON (Call.phone_number = Phone_book.phone_number)
  WHERE Phone_book.phone_number IS NULL

(ignorując to, jak powiedzieli inni, zwykle najlepiej wybrać tylko te kolumny, które chcesz, a nie ' *')

— Alnitak
źródło

1

unikaj IN, użyj EXISTS - wskazówka znajduje się w tytule pytania

— annakata

28

Lewe sprzężenie zewnętrzne jest prawdopodobnie najszybsze w ogólnym przypadku, ponieważ zapobiega ponownemu wykonaniu podzapytania.

— WOPR

Nie wybredny, ale podzapytanie z mojej sugestii zwraca <code> wybierz „x” </code>, a nie <code> wybierz * </code>

— Alterlife

tak - instrukcja MySQL sugeruje, że jest to normalne w przypadku zapytania „ISTNIEJE”

— Alnitak,

2

@Alnitak: W drugim zapytaniu nie potrzebujesz SELECT *w podzapytaniu. Zamiast tego, na przykład SELECT 1, powinno wystarczyć.

— Alexander Abakumov

90

SELECT Call.ID, Call.date, Call.phone_number 
FROM Call 
LEFT OUTER JOIN Phone_Book 
  ON (Call.phone_number=Phone_book.phone_number) 
  WHERE Phone_book.phone_number IS NULL

Powinien usunąć podkwerendę, pozwalając optymalizatorowi zapytań na działanie swojej magii.

Unikaj również opcji „WYBIERZ *”, ponieważ może to spowodować uszkodzenie kodu, jeśli ktoś zmieni bazowe tabele lub widoki (i jest to nieefektywne).

— WOPR
źródło

10

Jest to na ogół najskuteczniejsza metoda, ponieważ nie wykonuje wielu przejść na drugim stole ... mam nadzieję, że niektórzy czytają komemnty.

— Nerdfest,

3

Wolałbym mieć nadzieję, że ludzie profilują się: chyba że jesteś najlepszym guru wydajności SQL, uprzedzenie, co będzie najszybsze, jest dość trudne (i zależy od używanego silnika DBMS).

— bortzmeyer

2

Notacja Big O z łatwością powie ci, czego możesz spodziewać się w tym przypadku najszybciej. To rzędy wielkości różne.

— Jonesopolis

Zobacz odpowiedź Afterlife i mój komentarz tam, jeśli istnieje 1:Nzwiązek między twoimi dwiema tabelami. LUB dodaj, DISTINCTjak widać w odpowiedzi Vlado

— ToolmakerSteve

25

Poniższy kod byłby nieco bardziej wydajny niż odpowiedzi przedstawione powyżej w przypadku większych zestawów danych.

SELECT * FROM Call WHERE 
NOT EXISTS (SELECT 'x' FROM Phone_book where 
Phone_book.phone_number = Call.phone_number)

— Alterlife
źródło

1

Jak zawsze warto profilować wydajność zapytań względem docelowego zestawu danych, aby wybrać ten o najlepszej wydajności. Optymalizatory SQL są obecnie na tyle dobre, że wyniki wydajności są często zaskakujące.

— Greg Hewgill,

1

Zaletą tego podejścia (w porównaniu do LEFT OUTER JOIN firmy WOPR) jest to, że pozwala uniknąć zwracania wielu wierszy na wiersz Call, jeśli jest wiele pasujących wierszy Phone_book. To znaczy, jeśli istnieje 1:Nzwiązek między twoimi dwiema tabelami.

— ToolmakerSteve

Zacznę od tego - bezpośrednio reprezentuje intencję. Jeśli wydajność nie jest wystarczająco dobra, upewnij się, że istnieją odpowiednie indeksy. Tylko wtedy wypróbuj mniej oczywiste LEFT OUTER JOIN, sprawdź, czy jego wydajność jest lepsza.

— ToolmakerSteve

6

SELECT DISTINCT Call.id 
FROM Call 
LEFT OUTER JOIN Phone_book USING (id) 
WHERE Phone_book.id IS NULL

Zwróci to dodatkowe identyfikatory, których brakuje w tabeli Książka telefoniczna.

— Vlado
źródło

4

Myślę

SELECT CALL.* FROM CALL LEFT JOIN Phone_book ON 
CALL.id = Phone_book.id WHERE Phone_book.name IS NULL

— Nat Geo
źródło

idKolumna w calltabeli nie jest taka sama wartość jak idkolumna w Phone_booktabeli, więc nie można dołączyć na tych wartościach. Zobacz odpowiedź WOPR na podobne podejście.

— Michael Fredrickson

3

SELECT t1.ColumnID,
CASE 
    WHEN NOT EXISTS( SELECT t2.FieldText  
                     FROM Table t2 
                     WHERE t2.ColumnID = t1.ColumnID) 
    THEN t1.FieldText
    ELSE t2.FieldText
END FieldText       
FROM Table1 t1, Table2 t2

— Harvinder Sidhu
źródło

Spowoduje to zwrócenie danych z jednej tabeli, jeśli danych nie ma w innej tabeli dla tej samej kolumny

— Harvinder Sidhu,

1

SELECT name, phone_number FROM Call a
WHERE a.phone_number NOT IN (SELECT b.phone_number FROM Phone_book b)

— JoshYates1980
źródło

To nie daje odpowiedzi na pytanie. Aby skrytykować lub poprosić autora o wyjaśnienia, zostaw komentarz pod postem. - Z recenzji

— Dennis Kriechel,

@DennisKriechel zaktualizował zapytanie, aby było bardziej szczegółowe dla pytania.

— JoshYates1980,

1

Alternatywnie,

select id from call
minus
select id from phone_number

— elfekz
źródło

1

Nie jestem pewien, czy to odpowiada na obecne pytanie (chociaż MINUS) jest nowym dodatkiem. Skończyło się to w kolejce niskiej jakości - możesz poprawić tę odpowiedź.

— ste-fu