Come usare SQL OFFSET

La risposta rapida: come usare SQL OFFSET

La clausola OFFSET in SQL viene usata per saltare un numero specifico di righe prima di restituire le righe nel set di risultati. Spesso viene usata in combinazione con la clausola LIMIT per impaginare i risultati.

SELECT column_names
FROM table_name
ORDER BY column_name
LIMIT number_of_rows OFFSET offset_value;

Nella query SQL sopra, number_of_rows indica lo specifico numero di righe da restituire, mentre offset_value indica quante righe la query deve saltare prima di restituirle. 

Come funziona OFFSET

La clausola OFFSET salta il numero specificato di righe nel set di risultati. Quando usata con ORDER BY, garantisce risultati coerenti saltando le righe in un ordine definito, di solito specificato da un nome di colonna. Per impostazione predefinita, ORDER BY restituisce la colonna specificata in ordine crescente.

Supponiamo di voler interrogare dalla tabella employees le seguenti colonne: employee_id, first_name, last_name, phone_number e salary. Vuoi anche escludere le prime 9 righe dai risultati. Userai la query SQL qui sotto.

-- Select all columns for employees, starting from the 10th row
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Order by employee_id to ensure consistent ordering
ORDER BY employee_id
-- Skip the first 9 rows
OFFSET 9 ROWS;

È importante ricordare che, a causa di colonne di ordinamento non univoche, occorre prestare attenzione per evitare record duplicati.

Combinare LIMIT e OFFSET

OFFSET viene in genere usato con LIMIT per controllare quante righe restituire e da dove iniziare a restituirle. 

Nell'esempio seguente, la clausola LIMIT restituisce solo le 10 righe successive dopo aver saltato le prime 9.

-- Select all columns for employees, starting from the 10th row and fetching the next 10 rows
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Order by employee_id to ensure consistent ordering
ORDER BY employee_id
-- Skip the first 9 rows
OFFSET 9
-- Fetch the next 10 rows
LIMIT 10;

Applicazioni pratiche di OFFSET

SQL OFFSET è ampiamente usato per la paginazione nelle applicazioni web. La paginazione dei dati, per chiarezza, è una tecnica per suddividere grandi quantità di dati in blocchi più piccoli, che aiuta gli utenti a navigare in modo efficiente in dataset di grandi dimensioni. 

Considero la paginazione dei dati uno dei migliori esempi di utilizzo di OFFSET e LIMIT. Con questa tecnica, le clausole OFFSET e LIMIT usate insieme suddividono i dati in blocchi gestibili visualizzati su più pagine.  

Questo migliora l'esperienza utente perché nessuno si trova di fronte a un elenco enorme tutto in una volta. 

Altre applicazioni di OFFSET includono:

1. Saltare determinati record durante l'analisi dei dati.
2. Preparare i dati per l'esportazione a blocchi (quando usato in combinazione con LIMIT).
3. Concentrarsi semplicemente sui dati in un intervallo specifico, quando sai cosa stai cercando. 

Supporto specifico dei database e alternative

Diversi database supportano le clausole OFFSET e LIMIT, tra cui MySQL, PostgreSQL e SQLite. Metodi alternativi, come FETCH, possono essere usati per i database che non supportano le istruzioni OFFSET e LIMIT, come SQL Server.

Per esempio, la query seguente (che usa LIMIT) restituirebbe valori nei database MySQL, SQLite e PostgreSQL ma non in SQL Server.

-- Select all columns for employees, starting from the 10th row and fetching the next 10 rows
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Order by employee_id to ensure consistent ordering
ORDER BY employee_id
-- Skip the first 9 rows
OFFSET 9
-- Fetch the next 10 rows
LIMIT 10;

Pertanto, in SQL Server dovresti usare FETCH per ottenere risultati simili con la query seguente;

-- Select specific columns for employees, starting from the 10th row and fetching the next 10 rows
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Order by employee_id to ensure consistent ordering
ORDER BY employee_id 
-- Skip the first 9 rows (starting from the 10th row)
OFFSET 9 ROWS 
 -- Fetch the next 10 rows
FETCH NEXT 10 ROWS ONLY;

Tecniche avanzate: OFFSET e FETCH

La clausola FETCH, usata nello standard SQL, può essere un'alternativa avanzata a LIMIT. Mentre OFFSET specifica quante righe saltare, FETCH indica quante righe restituire dalla query.

-- Selecting columns from employees table
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Ordering the result set by employee_id for consistent pagination
ORDER BY employee_id
-- Skipping the first 20 rows in the result set
OFFSET 20 ROWS
-- Fetching the next 10 rows only after the offset
FETCH NEXT 10 ROWS ONLY;

Altre tecniche SQL avanzate si trovano nel nostro corso Associate Data Analyst in SQL.

Considerazioni sulle prestazioni e altre best practice 

Usare OFFSET in modo efficace richiede di comprenderne l'impatto sulle prestazioni delle query. 

OFFSET può influire sulle prestazioni delle query, soprattutto con dataset di grandi dimensioni. SQL Server gestisce in modo efficiente la cache e l'esecuzione delle query, ma usare OFFSET con dataset grandi può richiedere strategie di ottimizzazione aggiuntive, come l'uso di Common Table Expressions (CTE) o tabelle temporanee. Pertanto, devi monitorare le prestazioni delle query per individuare problemi e risolvere i colli di bottiglia che ostacolano l'esecuzione. Il corso Intermediate SQL Server di DataCamp include un capitolo sulle common table expression e altri argomenti avanzati. 

Ecco alcune ulteriori best practice che ho scoperto nella mia esperienza.

• Usa sempre una clausola ORDER BY adeguata. Questo approccio garantisce che i record vengano ordinati in modo coerente.
• Valuta tecniche di indicizzazione e ottimizzazione delle query. Questo metodo assicura prestazioni ottimizzate, soprattutto quando lavori con database di grandi dimensioni.

Conclusione e approfondimenti

Padroneggiare la clausola OFFSET, combinata con LIMIT, è fondamentale per una gestione efficiente dei dati. Sperimenta queste clausole nelle tue query SQL per migliorare le prestazioni e l'esperienza utente. Puoi approfondire la clausola OFFSET in SQL seguendo i corsi Intermediate SQL e Fondamenti di SQL di DataCamp.

Allo stesso modo, puoi approfondire l'analisi dei dati con SQL con il nostro corso Reporting in SQL. Inoltre, se vuoi dimostrare le tue competenze pratiche con progetti SQL, ti consiglio di ottenere la Certificazione SQL Associate per distinguerti ai colloqui.