Wat is het verschil tussen NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

Het lijkt mij dat je hetzelfde kunt doen in een SQL-query met NOT EXISTS, NOT IN of LEFT JOIN WHERE IS NULL. Bijvoorbeeld:

SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)
SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)
SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL

Ik weet niet zeker of ik alle syntaxis correct heb, maar dit zijn de algemene technieken die ik heb gezien. Waarom zou ik ervoor kiezen om de ene boven de andere te gebruiken? Verschillen de prestaties…? Welke van deze is het snelst / meest efficiënt? (Als het afhangt van de implementatie, wanneer zou ik ze dan allemaal gebruiken?)

Antwoord 1, autoriteit 100%

• NIET IN vs. NIET BESTAAN vs. LEFT JOIN / IS NULL: SQL Server

• NIET IN vs. NIET BESTAAN vs. LEFT JOIN / IS NULL: PostgreSQL

• NIET IN vs. NIET BESTAAN vs. LEFT JOIN / IS NULL: Oracle

• NIET IN vs. NIET BESTAAN vs. LEFT JOIN / IS NULL: MySQL

In een notendop:

NOT INis een beetje anders: het komt nooit overeen als er maar één NULLin de lijst staat.

• In MySQLis NOT EXISTSiets minder efficiënt

• In SQL Serveris LEFT JOIN / IS NULLminder efficiënt

• In PostgreSQLis NOT INminder efficiënt

• In Oraclezijn alle drie de methoden hetzelfde.

Antwoord 2, autoriteit 4%

Als de database goed is in het optimaliseren van de zoekopdracht, worden de eerste twee getransformeerd naar iets dat dicht bij de derde ligt.

Voor eenvoudige situaties zoals die in je vraag, zou er weinig of geen verschil moeten zijn, omdat ze allemaal als joins worden uitgevoerd. Bij complexere zoekopdrachten kan de database mogelijk geen join maken van de zoekopdrachten not inen NOT EXISTS. In dat geval worden de zoekopdrachten een stuk langzamer. Aan de andere kant kan een join ook slecht presteren als er geen index is die kan worden gebruikt, dus alleen omdat je een join gebruikt, wil nog niet zeggen dat je veilig bent. U zou het uitvoeringsplan van de query moeten onderzoeken om te zien of er prestatieproblemen zijn.

Antwoord 3

Ervan uitgaande dat u nulls vermijdt, zijn dit allemaal manieren om een ​​anti-jointe schrijven met behulp van standaard SQL.

Een voor de hand liggende omissie is het equivalent met EXCEPT:

SELECT a FROM table1
EXCEPT
SELECT a FROM table2

Opmerking in Oracle dat u de operator MINUSmoet gebruiken (misschien een betere naam):

SELECT a FROM table1
MINUS
SELECT a FROM table2

Over propriëtaire syntaxis gesproken, er kunnen ook niet-standaard equivalenten zijn die het onderzoeken waard zijn, afhankelijk van het product dat u gebruikt. OUTER APPLYin SQL Server (zoiets als):

SELECT t1.a
  FROM table1 t1
       OUTER APPLY 
       (
        SELECT t2.a
          FROM table2 t2
         WHERE t2.a = t1.a
       ) AS dt1
 WHERE dt1.a IS NULL;

Antwoord 4

Als u gegevens in een tabel moet invoegen met een primaire sleutel met meerdere velden, denk er dan aan dat het veel sneller zal zijn (ik heb geprobeerd in Access, maar ik denk in elke database) om niet te controleren of er geen records bestaan met ‘dergelijke’ waarden in table”, – liever gewoon in de tabel invoegen, en overtollige records (met de toets) worden niet twee keer ingevoegd.

Antwoord 5

Prestatieperspectief vermijd altijd het gebruik van inverse zoekwoorden zoals NOT IN, NOT EXISTS, …
Omdat om de inverse items te controleren, DBMS door alle beschikbare moet lopen en de inverse selectie moet laten vallen.