Firebird Documentation Index → Guida sull'uso di NULL nel linguaggio SQL di Firebird → I predicati
Firebird Home Firebird Home Indietro: Funzioni interne ed altre direttiveFirebird Documentation IndexRisali: Guida sull'uso di NULL nel linguaggio SQL di FirebirdAvanti: Ricerche

I predicati

Il predicato IN
I quantificatori ANY, SOME e ALL
EXISTS e SINGULAR

I predicati sono frasi con oggetti che riportano un valore booleano: true, false oppure sconosciuto (= NULL). Nella programmazione si possono trovare i predicati nelle posizioni in cui bisogna prendere delle decisioni o fare delle scelte. Nel SQL di Firebird, ciò significa nelle frasi contenenti le clausole WHERE, HAVING, CHECK, CASE WHEN, IF e WHILE.

Anche i confronti come «x > y» danno come risultato valori booleani, ma non sono generalmente chiamati predicati, sebbene sia questione di forma. Un'espressione come Maggiore(x, y) che esprime la stessa cosa verrebbe immediatamente riconosciuta come predicato. I matematici preferiscono dare un nome ai predicati – come «Maggiore» o solo «M» – ed una coppia di parentesi per raccogliere gli argomenti.

Firebird supporta i seguenti predicati SQL: IN, ANY, SOME, ALL, EXISTS e SINGULAR.

Nota

È accettabile dire che «IS [NOT] NULL» e «IS [NOT] DISTINCT FROM» sono predicati, nonostante l'assenza di parentesi. Ma che siano predicati o no, ne abbiamo già parlato e non ne parleremo ancora in questa sezione.

Il predicato IN

Il predicato IN confronta l'espressione alla sua sinistra con le espressioni passate in una lista come argomenti e riporta true se trova una corrispondenza. NOT IN riporta sempre l'opposto di IN. Alcuni esempi del suo uso sono: 

select NumAula, Piano from Classi where Piano in (3, 4, 5)
delete from Clienti where upper(NomeCliente) in ('IGNOTO', 'NN', 'BOH', '' )
if ( A not in (Var1, Var1 + 1, Var2, Var3 )) then ...

La lista può essere anche generata da una subquery ad un solo campo: 

select Num, Nome, Classe from Studenti
  where Num in (select distinct Assegnatario from PrestitoLibri)

Con una lista vuota

Se la lista è vuota (ciò è possibile solo con una subquery), IN riporta sempre false e NOT IN sempre true, anche se l'espressione da valutare è NULL. Il senso è questo: anche se un valore è ignoto, sicuramente non c'è in una lista vuota.

Quando è NULL l'espressione di confronto

Se la lista non è vuota e l'espressione di test, che chiameremo «A» nei successivi esempi, è invece NULL, i predicati seguenti riporteranno sempre NULL, indipendentemente dalle espressioni nella lista:

  • A IN ( Expr1, Expr2, ..., ExprN )

  • A NOT IN ( Expr1, Expr2, ..., ExprN )

Il primo esempio può essere compreso pensando l'intera espressione come una catena di disgiunzioni (funzioni OR) di test sull'uguaglianza:

A=Expr1 or A=Expr2 or ... or A=ExprN

che pertanto, se A è NULL, diventa

NULL or NULL or ... or NULL

cioè NULL.

Il secondo predicato viene determinato dal fatto che «not (NULL)» è uguale a NULL.

Quando NULL è nella lista

Se A ha un valore proprio ma la lista contiene una o più espressioni NULL, le cose diventano un po' più complicate:

  • Se almeno una delle espressioni della lista ha lo stesso valore di A:

    • «A IN( Expr1, Expr2, ..., ExprN )» vale true

    • «A NOT IN( Expr1, Expr2, ..., ExprN )» vale false

    Questo lo si deve al fatto che «true or NULL» vale true (vedi sopra). Più in generale: una disgiunzione dove almeno uno degli elementi è true, riporta true perfino se altri elementi sono NULL. Ovviamente tutti i false eventualmente presenti non interessano. In una disgiunzione interessa se c'è almeno un true

  • Se nessuna delle espressioni in lista ha lo stesso valore di A:

    • «A IN( Expr1, Expr2, ..., ExprN )» vale NULL

    • «A NOT IN( Expr1, Expr2, ..., ExprN )» vale NULL

    Questo perchè «false or NULL» riporta NULL. Generalizzando si può dire che una disgiunzione che ha solo elementi false e NULL, vale NULL.

Inutile dire che se né A né ciascuna espressione in lista è NULL, il risultato è quello atteso e può essere solo o true o false.

I risultati di IN()

La tabella seguente mostra tutti i possibilirisultati per IN e NOT IN. Per usarla nel modo giusto, si parte dalla prima domanda a sinistra. Se la risposta è No, passare alla linea successiva. Se la risposta è Si, leggere il risultato dalla seconda o terza colonna, come appropriato, ed è tutto.

Tabella 3. Results for «A [NOT] IN (<list>)»

Conditions Results
IN() NOT IN()
La lista è vuota? false true
Altrimenti, A è NULL? NULL NULL
Altrimenti, esiste almeno un elemento della lista uguale ad A? true false
Altrimenti, almeno un elemento della lista è NULL? NULL NULL
Altrimenti tutti gli elementi della lista sono diversi da A e non NULL, quindi false true


In alcuni contesti (ad esempio nelle clausole IF e WHERE), un risultato NULL si comporta come se ci fosse false nel senso che la condizione non è soddisfatta quando l'espressione di controllo è NULL. Ciò conviene nei casi dove ci si aspetterebbe false ma invece c'è NULL: semplicemente non si nota la differenza. Il rovescio della medaglia è che ci si aspetterebbe true quando l'espressione viene invertita (usando NOT) ed invece si casca dall'asino nel senso che l'espressione più «pericolosa» fra i casi elencati nella tabella qui sopra è del tipo «A NOT IN (<lista>)», dove A non è presente nella lista (cioè ci si aspetterebbe true) ma nella lista succede che ci siano uno o più NULL.

Attenzione

Bisogna stare particolarmente attenti ad usare NOT IN in una subselect invece che in una lista esplicita, cioè: 

A not in ( select Numeri from Tabella )

Se A non è presente nella colonna Numeri, il risultato è true se nessuno dei Numeri è NULL, ma è NULL se c'è un NULL fra i Numeri. Attenzione inoltre che perfino in situazioni dove A è costante ed il suo valore non è mai contenuto nella colonna Numeri, il risultato dell'espressione (e di conseguenza il flusso del programma) può cambiare nel tempo con la presenza o l'assenza di NULL nella colonna. Divertitevi a beccare l'errore in debug! Naturalmente il problema si evita facilmente con l'aggiunta di «where Numeri is not NULL» alla subselect.

Problema

Tutte le versioni precedenti alla 2.0 contengono un problema per cui [NOT] IN riporta il risultato errato se è attivo un indice nella subselect e risulta vera una delle seguenti condizioni:

  • A è NULL e la subselect non riporta nessun NULL

  • A non è NULL e la subselect non contiene A ma contiene dei NULL.

Notare che un indice può essere attivo anche se non è stato creato esplicitamente, ad esempio se è stata definita una chiave con il campo A.

Esempio: la tabella TA ha una colonna A con valori { 3, 8 }. La tabella TB ha una colonna B con i valori { 2, 8, 1, NULL }. Entrambe le espressioni 

A [not] in ( select B from TB )

dovrebbero riportare NULL quando A = 3, a causa del NULL in B. Ma se B è indicizzato, IN riporta false e NOT IN riporta true. Come conseguenza, la query 

select A from TA where A not in ( select B from TB )

riporta un dataset con un record di un campo contenente il valore 3, mentre avrebbe dovuto riportare un dataset vuoto (nessun record). Altri errori possono esser dietro l'angolo, cioè usando «NOT IN» in una frase o una clausola tipo IF, CASE o WHILE.

Come alternativa a NOT IN, si può usare «<> ALL». Il predicato ALL lo vedremo tra breve.

IN() nei controlli di CHECK

Il predicato IN() è usato spesso nei controlli di CHECK. In tali contesti, le espressioni NULL hanno un effetto sorprendentemente differente nelle versioni di Firebird 2.0 e successive. Questo verrà dettagliato nella sezione dedicata ai controlli di CHECK.

I quantificatori ANY, SOME e ALL

Firebird ha due quantificatori che permettono di comparare un valore al risultato di una subselect:

  • ALL riporta true se il confronto è vero (cioè true) per ogni elemento della subselect.

  • ANY e SOME (completamente sinonimi) sono veri se il confronto è true per almeno uno degli elementi della subselect.

Con ANY, SOME e ALL si può gestire meglio l'operatore di confronto. Questo rende il tutto più flessibile che con IN, che supporta solo l'operatore (implicito ) «=». D'altra parte, ANY, SOME e ALL accettano solo subselect come argomento e non è possibile dare una lista di elementi esplicita come per IN.

Gli operatori validi sono =, !=, <, >, =<, => e tutti i loro sinonimi. Non si possono usare LIKE, CONTAINING, IS DISTINCT FROM, ed altri operatorior.

Alcuni esempi d'uso tipico:

select nome, stipendio from operai
  where stipendio > any( select stipendio from dirigenti )

(riporta la lista degli operai che guadagnano più di almeno un dirigente) 

select nome, provincia from operai
  where provincia != all( select distinct provincia from dirigenti )

(riporta la lista degli operai che non vivono in nessuna città in cui vive un dirigente) 

if ( StipendioDirigente !> some( select stipendio from operai ) )
  then Dirigentaccio = 1;
  else Dirigentaccio = 0;

(mette Dirigentaccio a 1 se lo stipendio di almeno un operaio è non minore del valore di StipendioDirigente)

I risultati di ANY, SOME e ALL

Se la subselect non riporta record, ALL vale true e ANY|SOME valgono false, anche se la parte sinistra dell'espressione è NULL. Questo segue naturalmente dalle definizioni e dalle regole della logica formale. Le menti matematiche avranno già notato che ALL è equivalente al quantificatore universale «∀» e che ANY|SOME lo sono a quello esistenziale «∃».

Per gli insiemi non vuoti, si può scrivere «A <op> ANY|SOME (<subselect>)» come

A <op> E1 or A <op> E2 or ... or A <op> En

dove <op> è l'operatore usato e E1, E2 ecc. sono i valori riportati dalla subquery.

Allo stesso modo, «A <op> ALL (<subselect>)» è lo stesso di

A <op> E1 and A <op> E2 and ... and A <op> En

Tutto questo ha l'aria familiare: la prima riscrittura è uguale a quella del predicato IN, cambia l'operatore che può essere ora diverso dal solito «=». La seconda è diversa ma ne mantiene la forma generale. Adesso si può determinare come in A e/o nel risultato della subselect, il NULL modifica il comportamento di ANY|SOME e ALL. Questo verrà mostrato allo stesso moso di come abbiamo visto prima per IN, solo che adesso invece di mostrare il procedimento passo passo, andiamo direttamente a presentare la tabella dei risultati. Al solito, leggere le domande nella colonna di sinistra dall'alto inbasso e, non appena la risposta è «si», ricavare il risultato dalla colonna di destra.

Tabella 4. Risultati per «A <op> ANY|SOME (<subselect>)»

Conditizioni Risultati
ANY | SOME
La subselect non ha riportato niente (cioè nessuna riga)?  
altrimenti, A è NULL? NULL
altrimenti, uno dei confronti almento è vero, cioè riporta true? true
altrimenti, uno dei confronti almeno riporta NULL? NULL
altrimenti ( tutti i confronti sono falsi, e riportano false) false


A pensare che questi risultati siano proprio simili a quelli visti con IN(), non ci si sbaglia molto: con l'operatore «=», ANY è si comporta come IN. Allo stesso modo, «<> ALL» è equivalente a NOT IN.

Problemi rivisitati

Nelle versioni precedenti alla 2.0, «= ANY» aveva lo stesso problema di IN: in «certe» circostanze, si hanno risultati sbagliati con espressioni del tipo «NOT A = ANY( ... )».

Il lato buono è che , «<> ALL» non ha quel problema e da' sempre i risultati giusti.

Tabella 5. Risultati per «A <op> ALL (<subselect>)»

Condizioni Risultati
ALL
La subselect non ha riportato niente (cioè nessuna riga)? true
altrimenti, A è NULL? NULL
altrimenti, uno dei confronti almento è falso, cioè riporta false? false
altrimenti, uno dei confronti almeno riporta NULL? NULL
altrimenti ( tutti i confronti sono veri, e riportano true) true


Problemi di ALL

Per quanto «<> ALL» faccia sempre il suo dovere, nondimeno ALL, in tutte le versioni di Firebird precedenti alla 2.0, non è scevro da problemi: qualsiasi altro operatore che non sia «<>», può dare risultati errati se è attivo un indice nella subselect, indipendentemente dalla presenza di NULL.

Nota

Parlando papale papale, il secondo punto in entrambe le tabelle («A è NULL?») è rindondante e potrebbe essere eliminato. Se A è NULL, tutti i confronti riportano NULL, pertanto la situazione potrebbe essere rinviata alle domande successive. E già che ci siamo, si potrebbe eliminare pure la prima domanda: il caso "senza righe" è un caso particolare dell'ultima. Il tutto ancora una volta diventa «true batte il NULL che batte il false» nelle disgiunzioni (ANY|SOME) e «false batte il NULL che batte il true» nelle congiunzioni (ALL).

La ragione per includere tutti i dettagli è la praticità: si riesce a vedere a colpo d'occhio se non ci sono record, ed è ancor più facile verificare che la parte sinistra di un'espressione sia NULL piuttosto che valutare ogni singolo confronto dei risultati. Si può fare come si crede, saltare entrambi i punti o solo il secondo. Ad ogni modo, non va ignorata la prima domanda iniziando con la seconda: questo porta a conclusioni errate se la subselect è vuota!

EXISTS e SINGULAR

I predicati EXISTS e SINGULAR danno informazioni su una subquery, di solito correlata. Si possono usare nelle clausole WHERE, HAVING, CHECK, CASE, IF e WHILE (le ultime due sono disponibili solo in PSQL, il linguaggio di Firebird dedicato alle stored procedure ed ai trigger).

EXISTS

EXISTS dice se una subquery riporta almeno una riga di dati. Per ottenere ad esempio una lista di lavoratori che sono anche proprietari, si potrebbe avere una cosa del genere: 

SELECT Lavoratore FROM Aziende WHERE EXISTS
  (SELECT * FROM Proprietari
   WHERE Proprietari.Nome = Aziende.Lavoratore)

Questa query riporta i nomi di tutti i lavoratori che sono anche nella tabella proprietari. Il predicato EXISTS riporta true se l'insieme di righe risultanti dalla subselect ne contiene almento una, se invece è vuoto, EXISTS riporta false. EXISTS non riporta mai NULL, in quanto un result set o c'ha righe o non ce n'ha. Può succedere che le condizioni di ricerca della subselect possano dare tutto a NULL per certe righe, ma non crea incertezze perchè tali righe non vengono incluse nel risultato.

Nota

La subselect in realtà non riporta nessun result set. Il sistema semplicemente scorre la subselect (la tabella Proprietari dell'esempio) record per record applicando la condizione di ricerca. Se trova che vale true, EXISTS riporta true immediatamente e i record rimanenti non vengono guardati. Se è false o NULL, prosegue con il successivo record. Se sono stati controllati tutti i record e non è stato trovato neanche l'ultimo controllo ha dato true, EXISTS riporta false.

NOT EXISTS da' sempre l'opposto di EXISTS: false oppure true, mai NULL. NOT EXISTS riporta false immediatamente non appena ha un risultato true nella condizione di ricerca. Per riportare true deve scorrersi tutti i record.

SINGULAR

SINGULAR è una estensione allo standard SQL di InterBase/Firebird. Si può dire che riporta true se nella subquery esattamente un record soddisfa la condizione. Per analogia con EXISTS ci si aspetterebbe che anche SINGULAR possa riportare solo o true o false. Dopo tutto un result set o ha esattamente un record oppure ne ha un numero differente. Purtroppo tutte le versioni di Firebird fino alla 2.0 compresa hanno un problema che provoca dei risultati NULL in un certo numero di casi anomali. Il comportamento è abbastanza inconsistente, ma allo stesso tempo perfettamente riproducibile: per esempio, in una colonna contenente (1, NULL, 1), un test per SINGULAR con la condizione «A=1» riporta NULL, ma lo stesso test con i dati (1, 1, NULL) riporta false. Notare che cambia solo l'ordine di inserimento dei dati!

A render le cose peggiori, in tutte le versioni precedenti la 2.0 talvolta viene riportato NULL da NOT SINGULAR quando invece SINGULAR da' false o true. Nella 2.0, almeno questo non succede più: o è false invece di true o entrambi NULL.

Il codice è stato corretto nella versione 2.1 di Firebird; da quella versione in poi SINGULAR riporterà:

  • false se la condizione di ricerca non è mai true (includendo il caso dell'insieme vuoto, cioè nessun record);

  • true se la condizione di ricerca è vera per esattamente 1 record;

  • false se la condizione di ricerca è vera per più di 1 record.

Se altri record sono false, NULL o una combinazione di questi, non ha più importanza.

NOT SINGULAR riporterà sempre l'esatto opposto di SINGULAR (come già succede nella 2.0, solo che il valore NULL è adesso impossibile).

Nel frattempo che la 2.1 diventi definitiva, se c'è anche una qualsiasi piccola possibilità che la ricerca possa riportare NULL per una o più righe, bisogna sempre aggiungere una condiziione di IS NOT NULL alla clausola [NOT] SINGULAR come, ad esempio 

... SINGULAR( SELECT * from Tabella
              WHERE Campo > 38
              AND Campo IS NOT NULL )
Indietro: Funzioni interne ed altre direttiveFirebird Documentation IndexRisali: Guida sull'uso di NULL nel linguaggio SQL di FirebirdAvanti: Ricerche
Firebird Documentation Index → Guida sull'uso di NULL nel linguaggio SQL di Firebird → I predicati