Le subquery e le JOIN

Le subquery spesso, ma non sempre, possono essere riscritte in forma di JOIN.

Riscrivere le subquery come JOIN

Le subquery che usano IN possono essere riscritte con la parola chiave DISTINCT. Per esempio:

SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table2);

può essere riscritta come:

SELECT DISTINCT table1.* FROM table1, table2 WHERE table1.col1=table2.col1;

Anche le subquery NOT IN e NOT EXISTS possono essere riscritte. Per esempio queste due query restituiscono gli stessi risultati:

SELECT * FROM table1 WHERE col1 NOT IN (SELECT col1 FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT col1 FROM table2 WHERE table1.col1=table2.col1);

ed entrambe possono essere scritte così:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

Le subquery che possono essere riscritte come LEFT JOIN a volte sono più efficienti.

Usare le subquery invece delle JOIN

Vi sono però scenari che esigono l'uso delle subquery invece delle JOIN:

• Quando si vogliono ottenere i duplicati, ma non i falsi duplicati. Si supponga che Table_1 abbia tre righe — {1,1,2} — e Table_2 ne abbia due — {1,2,2}. Se si desidera ottenere un elenco delle righe in Table_1 che si trovano anche in Table_2, solo questa SELECT restituirà la risposta corretta (1,1,2):

SELECT Table_1.column_1 
FROM   Table_1 
WHERE  Table_1.column_1 IN 
  (SELECT Table_2.column_1 
    FROM   Table_2);

• Questa istruzione SQL non va bene:

SELECT Table_1.column_1 
FROM   Table_1,Table_2 
WHERE  Table_1.column_1 = Table_2.column_1;

• perché il risultato è {1,1,2,2} — e la ripetizione di 2 è un errore. Nemmeno questa istruzione va bene:

SELECT DISTINCT Table_1.column_1 
FROM   Table_1,Table_2 
WHERE  Table_1.column_1 = Table_2.column_1;

• perché il risultato è {1,2} — e anche la rimozione del duplicato di 1 è un errore.

• Quando l'istruzione più esterna non è una query. L'istruzione SQL:

UPDATE Table_1 SET column_1 = (SELECT column_1 FROM Table_2);

• non può essere espressa tramite una JOIN, a meno che non si utilizzino certe rare caratteristiche di SQL3.

• Quando la JOIN è su un'espressione. L'istruzione SQL:

SELECT * FROM Table_1 
WHERE column_1 + 5 =
  (SELECT MAX(column_1) FROM Table_2);

• è difficile da tradurre in una JOIN. Infatti l'unico modo è questa istruzione:

SELECT Table_1.*
FROM   Table_1, 
      (SELECT MAX(column_1) AS max_column_1 FROM Table_2) AS Table_2
WHERE  Table_1.column_1 + 5 = Table_2.max_column_1;

• che però contiene ancora una query tra parentesi, perciò nella trasformazione non si guadagna nulla.

• Quando si desidera vedere l'eccezione. Per esempio, se la domanda è: quali sono i libri più lunghi di Das Kapital? Queste due query sono quasi equivalenti:

SELECT DISTINCT Bookcolumn_1.*                     
FROM   Books AS Bookcolumn_1 JOIN Books AS Bookcolumn_2 USING(page_count) 
WHERE  title = 'Das Kapital';

SELECT DISTINCT Bookcolumn_1.* 
FROM   Books AS Bookcolumn_1 
WHERE  Bookcolumn_1.page_count > 
  (SELECT DISTINCT page_count 
  FROM   Books AS Bookcolumn_2 
  WHERE  title = 'Das Kapital');

• La differenza è che, se vi sono due edizioni di Das Kapital (con un diverso numero di pagine), allora la self-join restituirà i libri che sono più lunghi dell'edizione più breve di Das Kapital. Questa potrebbe essere la risposta sbagliata, perché la domanda originale non chiedeva "... più lunga di UN libro chiamato Das Kapital" (sembra partire dal presupposto erroneo che esista una sola edizione).