0Pricing
SQL Academy · Lezione

Confrontare righe nella stessa tabella

Trovi coppie e relazioni

Confrontare righe nella stessa tabella è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è un self join

Un self join consiste nel collegare una tabella a se stessa. All'inizio può sembrare insolito, ma è una tecnica potente per confrontare le righe all'interno della stessa tabella.

Immagini una tabella employees in cui ogni dipendente ha un manager_id che fa riferimento a un'altra riga della stessa tabella. Un self join permette di associare ogni dipendente al proprio responsabile in un'unica query.

Configurare la tabella di esempio

Creiamo una semplice tabella employees da usare durante la lezione. Ogni riga contiene un id, un name, uno salary e un manager_id che fa riferimento a un altro dipendente della stessa tabella.

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(50),
  salary     INT,
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   90000, NULL),
  (2, 'Bob',     70000, 1),
  (3, 'Carol',   65000, 1),
  (4, 'Dave',    55000, 2),
  (5, 'Eve',     60000, 2),
  (6, 'Frank',   48000, 3);

Sintassi di base del self join

Per eseguire un self join, faccia riferimento due volte alla stessa tabella usando due alias diversi. Gli alias si comportano come se si avessero due copie separate della tabella. Occorre poi specificare la condizione di join che collega le due copie.

Qui assegniamo a employees l'alias e (il dipendente) e a m (il responsabile), quindi confrontiamo e.manager_id = m.id.

SELECT
  e.name   AS employee,
  m.name   AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Includere le righe al vertice con LEFT JOIN

Un normale INNER JOIN elimina le righe in cui manager_id è NULL, quindi il dipendente al vertice, Alice, il CEO, verrebbe escluso dai risultati.

Usi un LEFT JOIN in modo che compaia ogni dipendente, anche chi non ha un responsabile. Per questi dipendenti, la colonna del responsabile sarà semplicemente NULL.

SELECT
  e.name          AS employee,
  m.name          AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name NULLS LAST, e.name;

Trovare coppie che condividono una proprietà

I self join sono utili anche per trovare coppie di righe che condividono un valore. Ad esempio, può trovare tutte le coppie di dipendenti che riportano allo stesso responsabile.

La condizione a.id < b.id impedisce che nei risultati compaiano entrambe le coppie duplicate, come (Bob, Carol) e (Carol, Bob).

SELECT
  a.name       AS employee_1,
  b.name       AS employee_2,
  a.manager_id AS shared_manager
FROM employees a
JOIN employees b
  ON a.manager_id = b.manager_id
 AND a.id < b.id;

Confrontare gli stipendi tra le righe

Può usare un self join per confrontare valori numerici tra righe. La query seguente trova ogni dipendente il cui stipendio è superiore a quello del proprio responsabile: un classico controllo analitico.

SELECT
  e.name       AS employee,
  e.salary     AS emp_salary,
  m.name       AS manager,
  m.salary     AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Trovare le righe senza corrispondenze

Un self join con LEFT JOIN e un controllo WHERE ... IS NULL permette di trovare le righe che non hanno una controparte corrispondente. Qui troviamo i dipendenti che non hanno nessuno che riporti a loro: i nodi foglia della gerarchia.

SELECT e.name AS employee
FROM employees e
LEFT JOIN employees sub ON sub.manager_id = e.id
WHERE sub.id IS NULL
ORDER BY e.name;

Contare i collaboratori diretti

Collegando la tabella a se stessa e raggruppando sul lato del responsabile, può contare quanti collaboratori diretti ha ogni responsabile. I dipendenti con zero collaboratori vengono inclusi grazie a LEFT JOIN.

SELECT
  m.name           AS manager,
  COUNT(e.id)      AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

Usare le CTE per rendere più chiari i self join

Quando una query con self join diventa complessa, racchiuderla in una Common Table Expression (CTE) rende la logica più facile da leggere. Definisca una CTE per la gerarchia e poi la interroghi in modo chiaro.

WITH hierarchy AS (
  SELECT
    e.id         AS emp_id,
    e.name       AS employee,
    e.salary     AS emp_salary,
    m.name       AS manager,
    m.salary     AS mgr_salary
  FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id
)
SELECT *
FROM hierarchy
WHERE mgr_salary IS NOT NULL
  AND emp_salary > mgr_salary;

Self join su una tabella non gerarchica

I self join non sono limitati alle gerarchie di responsabili e dipendenti. L'esempio seguente usa una tabella products per trovare tutte le coppie di prodotti appartenenti alla stessa categoria, una tecnica utile per i motori di raccomandazione o per i controlli di similarità.

CREATE TABLE products (
  id       INT PRIMARY KEY,
  name     VARCHAR(50),
  category VARCHAR(30)
);

INSERT INTO products VALUES
  (1, 'Laptop',  'Electronics'),
  (2, 'Phone',   'Electronics'),
  (3, 'Tablet',  'Electronics'),
  (4, 'Shirt',   'Clothing'),
  (5, 'Jeans',   'Clothing');

SELECT
  a.name AS product_1,
  b.name AS product_2,
  a.category
FROM products a
JOIN products b
  ON a.category = b.category
 AND a.id < b.id;

Problemi comuni: prodotti cartesiani e duplicati

Due errori comuni nei self join sono i prodotti cartesiani (dimenticare completamente la condizione di join) e le coppie duplicate (usare a.id <> b.id invece di a.id < b.id).

Verifichi sempre che la condizione di join sia corretta e usi < invece di <> quando servono solo coppie non ordinate, non coppie ordinate.

-- Wrong: returns every ordered pair (A,B) AND (B,A)
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
                AND a.id <> b.id;

-- Correct: returns each unordered pair once
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
                AND a.id < b.id;

Verifica rapida

Verifichi la Sua comprensione dei self join e del confronto tra righe della stessa tabella.

Riepilogo della lezione

In questa lezione ha imparato a utilizzare i self join per confrontare righe all'interno della stessa tabella.

Punti chiave:

  • Assegni due alias alla tabella (ad esempio, e e m), in modo che il database le tratti come sorgenti separate.
  • Utilizzi INNER JOIN quando servono solo le righe che hanno una corrispondenza e LEFT JOIN quando desidera includere anche le righe senza corrispondenza.
  • Utilizzi a.id < b.id per evitare coppie non ordinate duplicate.
  • I self join sono utili per le gerarchie (manager/dipendente), le ricerche di similarità (stessa categoria) e qualsiasi confronto in cui sia necessario mettere in relazione due righe della stessa tabella.
  • Le CTE possono rendere molto più facili da leggere e mantenere le query complesse con self join.

Domande Frequenti

La lezione «Confrontare righe nella stessa tabella» è gratuita?

Sì — il testo completo di «Confrontare righe nella stessa tabella» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Confrontare righe nella stessa tabella»?

Trovi coppie e relazioni Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.

Quanto tempo richiede la lezione «Confrontare righe nella stessa tabella»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Che cos'è un self join
  2. Dipendenti e responsabili
  3. Confrontare righe nella stessa tabella
  4. Limiti dei self join
← Torna a SQL Academy