LIMIT fissa un tetto al numero di righe
LIMIT è la manopola più semplice dell'SQL: dice a SQLite "dammi al massimo questo numero di righe". Mettilo alla fine di una SELECT e otterrai fino a quel numero di risultati: non di più, magari di meno se la tabella non ha abbastanza righe.
Ottieni le prime tre righe. Quali tre, esattamente? Ecco il punto: senza un ORDER BY, SQLite sceglie l'ordine che gli fa più comodo. Oggi potrebbe essere l'ordine di inserimento; domani, dopo un aggiornamento o una modifica agli indici, potrebbe non esserlo. LIMIT da solo va bene per "mostrami un campione", ma appena l'ordine conta devi renderlo esplicito.
OFFSET salta le righe iniziali
Abbina LIMIT a OFFSET e puoi chiedere una fetta dal mezzo del risultato. OFFSET k scarta le prime k righe; poi LIMIT n restituisce fino a n righe di quelle rimaste.
Significa "salta due righe, restituisci le due successive": le righe 3 e 4 del risultato ordinato. Il modello mentale: WHERE filtra, ORDER BY ordina, OFFSET salta, LIMIT taglia. Vengono eseguiti in quest'ordine, e contano tutti.
La paginazione ha sempre bisogno di ORDER BY
L'uso più comune di LIMIT e OFFSET è la paginazione: dividere un lungo elenco in pagine da, per esempio, 20 righe l'una. La pagina 1 è LIMIT 20 OFFSET 0, la pagina 2 è LIMIT 20 OFFSET 20, e così via.
Due cose da notare. Primo, l'ORDER BY non è negoziabile: senza, "pagina 2" non ha un significato definito e le righe possono rimescolarsi tra un caricamento e l'altro. Secondo, la chiave di ordinamento include id come criterio di spareggio. Se due post hanno lo stesso created_at, ti serve una colonna univoca per dare loro un ordine deterministico, altrimenti le loro posizioni possono scambiarsi e una riga può finire nella pagina sbagliata.
Regola pratica: ordina per qualcosa di univoco, oppure per la tua colonna di ordinamento più un criterio di spareggio univoco.
Una forma abbreviata: LIMIT n, m
SQLite supporta una vecchia sintassi con la virgola per compatibilità con MySQL: LIMIT offset, count. Significa lo stesso di LIMIT count OFFSET offset, ma l'ordine è invertito ed è facile leggerla male.
-- Queste due sono equivalenti:
SELECT * FROM books LIMIT 10 OFFSET 20;
SELECT * FROM books LIMIT 20, 10; -- prima l'offset, poi il conteggio
La seconda forma è concisa, ma inganna chi si aspetta che il primo numero sia il conteggio. Resta su LIMIT n OFFSET k: è esplicito e si legge da sinistra a destra.
OFFSET senza LIMIT: il trucco LIMIT -1
OFFSET non è valido da solo: la grammatica di SQLite richiede che segua un LIMIT. Quindi come dici "salta le prime 10 righe e dammi tutto il resto"? La convenzione è LIMIT -1, che SQLite interpreta come "nessun limite superiore".
Qualsiasi LIMIT negativo ha lo stesso effetto, ma -1 è l'idioma consolidato. Lo vedrai soprattutto negli script che scorrono un risultato a pagine e vogliono una query "dammi il resto" per l'ultimo blocco.
La trappola delle prestazioni di OFFSET
Ecco la cosa che nessuno dice finché non ci sbatti contro: OFFSET non fa saltare lavoro a SQLite, gli fa saltare output. Per restituire le righe dalla 10.001 alla 10.020, il motore scorre comunque internamente le prime diecimila righe prima di iniziare a emettere risultati. Gli offset piccoli non costano nulla; quelli da decine o centinaia di migliaia diventano sensibilmente lenti.
Per la paginazione profonda, la soluzione standard è la keyset pagination (paginazione per chiave): invece di "salta N righe", ricordi la chiave di ordinamento dell'ultima riga e chiedi "le righe dopo questa".
Ogni pagina fa una ricerca tramite indice invece di scorrere tutto ciò che viene prima. Il prezzo: non puoi saltare a "pagina 47", puoi solo andare avanti nei dati. Per i feed a scorrimento infinito e i cursori delle API, è esattamente quello che vuoi.
La paginazione basata su OFFSET va bene per le tabelle di amministrazione e i risultati piccoli. Per tutto ciò che cresce senza limiti, passa alla keyset pagination.
Un esempio completo
Mettiamo tutto insieme: una query paginata con filtro, ordinamento e un criterio di spareggio deterministico:
Filtra i prodotti da ufficio, ordina per prezzo crescente con il nome come criterio di spareggio, prendi i primi due. Cambia OFFSET 0 in OFFSET 2 per la pagina 2. La query è breve, ma ogni clausola si guadagna il suo posto.
Prossimo passo: DISTINCT
LIMIT controlla quante righe tornano; DISTINCT controlla se i duplicati tornano o no. È la prossima clausola nella cassetta degli attrezzi della SELECT, ed è facilissimo usarla male: è il tema della prossima pagina.
Domande frequenti
Cosa fa LIMIT in SQLite?
LIMIT n limita a un massimo di n il numero di righe restituite da una SELECT. Viene applicato dopo WHERE, GROUP BY e ORDER BY, quindi limiti il risultato finale, non le righe che la query scansiona. SELECT * FROM users LIMIT 10 restituisce fino a dieci righe.
Come funziona OFFSET insieme a LIMIT in SQLite?
OFFSET k salta le prime k righe del risultato prima che LIMIT inizi a contare. Quindi LIMIT 10 OFFSET 20 restituisce le righe dalla 21 alla 30. Internamente SQLite deve comunque scorrere le righe saltate, ed è per questo che gli offset grandi diventano lenti.
Si può usare OFFSET senza LIMIT in SQLite?
Non direttamente: OFFSET è valido solo come parte di una clausola LIMIT. Il trucco è LIMIT -1 OFFSET k, dove -1 significa 'nessun limite superiore', così SQLite salta k righe e restituisce tutto il resto. È una stranezza da ricordare.
Perché le query paginate hanno bisogno di ORDER BY?
Senza ORDER BY, SQLite è libero di restituire le righe nell'ordine che preferisce, e quell'ordine può cambiare da una query all'altra. A quel punto la paginazione si rompe: la stessa riga può comparire a pagina 1 e a pagina 3, o sparire del tutto. Abbina sempre LIMIT/OFFSET a un ORDER BY su una colonna stabile e univoca.
