[SQL] Chiarimenti utilizzo any/all/exists

[john_revelator]

Ciao a tutti e buon anno come prima cosa.

Mi servirebbero dei chiarimenti circa l'utilizzo di any, all ed exists all'interno delle subquery. Ho guardato un pò in rete ma rimango pieno di dubbi.

Questo è uno dei tanti link che ho trovato
http://www.java2s.com/Code/SQL/Selec...ALLcommand.htm

nel quale viene riportato questo dump:

CREATE TABLE Articles (
   ArticleID SMALLINT NOT NULL PRIMARY KEY,
   ArticleTitle VARCHAR(60) NOT NULL,
   Copyright YEAR NOT NULL
)
ENGINE=INNODB;


INSERT INTO Articles VALUES (12786, 'How write a paper', 1934),
                            (13331, 'Publish a paper', 1919),
                            (14356, 'Sell a paper', 1966),
                            (15729, 'Buy a paper', 1932),
                            (16284, 'Conferences', 1996),
                            (17695, 'Journal', 1980),
                            (19264, 'Information', 1992),
                            (19354, 'AI', 1993);


CREATE TABLE Authors (
   AuthID SMALLINT NOT NULL PRIMARY KEY,
   AuthorFirstName VARCHAR(20),
   AuthorMiddleName VARCHAR(20),
   AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;


INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson'),
                           (1007, 'Jason', 'Carol', 'Oak'),
                           (1008, 'James', NULL, 'Elk'),
                           (1009, 'Tom', 'M', 'Ride'),
                           (1010, 'Jack', 'K', 'Ken'),
                           (1011, 'Mary', 'G.', 'Lee'),
                           (1012, 'Annie', NULL, 'Peng'),
                           (1013, 'Alan', NULL, 'Wang'),
                           (1014, 'Nelson', NULL, 'Yin');


CREATE TABLE AuthorArticle (
   AuthID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   PRIMARY KEY (AuthID, ArticleID),
   FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO AuthorArticle VALUES (1006, 14356), 
                              (1008, 15729), 
                              (1009, 12786), 
                              (1010, 17695),
                              (1011, 15729), 
                              (1012, 19264), 
                              (1012, 19354), 
                              (1014, 16284);

L'ho caricato in mysql e ho cercato di capirne la logica.

Nell'esempio viene eseguita questa query:

SELECT ArticleTitle, Copyright
FROM Articles
WHERE Copyright > ANY
   (
      SELECT b.copyright
      FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
         JOIN Authors AS a USING (AuthID)
      WHERE AuthorLastName='Yin'
   )
ORDER BY ArticleTitle;

la quale non rende alcun record.

La query interna invece

SELECT b.copyright
      FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
         JOIN Authors AS a USING (AuthID)
      WHERE AuthorLastName='Yin'

restituisce 1996.
Vado quindi a guardarmi la tabella articles e capisco che è giusto ottenere un result set vuoto in quanto 1996 è il copyright maggiore.

Se modifico la query in questo modo:

SELECT ArticleTitle, Copyright
FROM Articles
WHERE Copyright > ANY
   (
      SELECT b.copyright
      FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
         JOIN Authors AS a USING (AuthID)
      WHERE AuthorLastName='Ken'
   )
ORDER BY ArticleTitle;

la query interna mi restituisce 1980 e di conseguenza la query complessiva mi restituirà 3 record, quelli con copyright 1996,1993 e 1992.

Peraltro se riscrivo la query così

SELECT ArticleTitle, Copyright
FROM Articles
WHERE Copyright > 
   (
      SELECT b.copyright
      FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
         JOIN Authors AS a USING (AuthID)
      WHERE AuthorLastName='Ken'
   )
ORDER BY ArticleTitle;

cioè uguale e identica senza la clausola ANY ottengo lo stesso identico risultato. Qualcuno è in grado di spiegarmi, magari con qualche esempio pratico, l'effettiva utilità di queste clausole. Anche la clausola exists non mi è affatto chiara. Ciao e grazie per l'attenzione.

[sprik]

Vediamo se mi esce qualcosa di decente...con ANY indichi che la condizione deve essere valida per una qualsiasi tupla della subquery, cioè viene verificata la condizione per ciascuno degli elementi presenti nella ANY.
Con ALL indichi tutte le tuple della subquery, cioè la relazione è vera se sono vere tutte le singole condizioni.

Esempio ANY:

SELECT Cognome 
FROM Lavoratori
WHERE stipendio <= 2000 AND mansione = ANY
("Elettricista", "Muratore", "Nullafacente");

Cioè elencare tutti i lavoratori che hanno stipendio minore o uguale a 2000 euro con mansione uguale ad una fra quelle comprese in ANY.

Esempio ALL:

SELECT Cognome 
FROM Lavoratori
WHERE Mansione <> "Elettricista" AND Stipendio > ALL
(SELECT Stipendio FROM Lavoratori WHERE Mansione = "Elettricista");

Cioè Elencare tutti i lavoratori non elettricisti che hanno lo stipendio maggiore di tutti gli elettricisti.

EXISTS verifica l'esistenza di almeno una tupla nel risultato della subquery.
La negazione NOT EXISTS verifica la condizione opposta a EXISTS.

Esempio EXISTS

SELECT Cognome 
FROM Studenti S
WHERE NOT EXISTS
(SELECT * FROM Esami WHERE S.ID = Esami.idStudente);

Cioè visualizza tutti gli studenti che non hanno effettuato esami.

[john_revelator]

Ciao sprik. Intanto ti ringrazio per tutto il tempo che mi hai dedicato.
Ieri notte continuando a gugolare avevo trovato anche io qualche esempio più chiaro.
Il dump che ho postato era totalmente inutile ai fini dell'utilizzo di quei predicati visto che la query interna estraeva sempre un unico record specifico.

Avevo fatto anche io una semplice tabellina su cui fare delle prove (tralasciamo il fatto che si poteva utilizzare un id per la qualifica in una tabella a parte, non è quello il punto )

CREATE TABLE `stipendi` (
  `id` int(11) NOT NULL auto_increment,
  `qualifica` varchar(50) default NULL,
  `nome` varchar(50) default NULL,
  `stipendio` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `stipendi` */

insert into `stipendi` (`id`,`qualifica`,`nome`,`stipendio`) 
values (1,'operaio','mario',10),
       (2,'operaio','giovanni',20),
       (3,'dirigente','andrea',30),
       (4,'dirigente','luca',40),
       (5,'operaio','gianni',25),
       (6,'dirigente','teo',20);

Quindi, anche rifacendomi, ai tuoi esempi, se volessi trovare gli operai che guadagnano più di un qualunque dirigente dovrei scrivere:

select * from stipendi where qualifica = 'operaio'
and stipendio > any (select stipendio from stipendi where qualifica = 'dirigente')

Questa query mi restituirebbe giovanni che guadagna 25.

Nella mia ignoranza, sino a ieri, la stessa query l'avrei scritta così

select * from stipendi where qualifica = 'operaio'
and stipendio > (select min(stipendio) from stipendi where qualifica = 'dirigente')

Supponiamo di aggiungere un ulteriore record alla tabella stipendi.

insert into stipendi (qualifica,nome,stipendio) values ('operaio','antonio',50)

Adesso vogliamo conoscere gli operai che guadagnano più di qualsiasi dirigente. Quindi utilizzando il predicato all la mia query diventa:

select * from stipendi where qualifica = 'operaio'
and stipendio > all (select stipendio from stipendi where qualifica = 'dirigente')

Così come sopra, alla "mia" maniera, avrei scritto la stessa query così

select * from stipendi where qualifica = 'operaio'
and stipendio > (select max(stipendio) from stipendi where qualifica = 'dirigente')

A questo punto la mia domanda è: ci sono dei casi in cui si debba necessariamente ricorrere a questi predicati?

Anche la tua query

SELECT Cognome 
FROM Lavoratori
WHERE stipendio <= 2000 AND mansione = ANY
("Elettricista", "Muratore", "Nullafacente");

che utilizza any, in cosa si differenzia da

SELECT Cognome 
FROM Lavoratori
WHERE stipendio <= 2000 AND mansione IN
("Elettricista", "Muratore", "Nullafacente");

?

[sprik]

La scelta è del tutto arbitraria, al più si può fare una considerazione a livello di performance, o comunque considerando che le funzioni AVG, MIN, MAX, ecc sono più utilizzate con raggruppamenti (GROUP BY). Ma sono sempre ben utilizzate per altri scopi.

Teoricamente fra "= ANY" e "IN" non c'è differenza, come fra "<> ALL" e "NOT IN".
All'atto pratico, fra i miei vecchi ricordi di 2 annetti fa, mi viene in mente che c'era qualcosa fra "= ANY" e "IN" che li differenziava, ma non ricordo se era del tipo che nella IN i parametri devono essere scritti e non possono essere il risultato di una subquery. Poi non vorrei dire cretinate, ma ricordo che appunto ho avuto questo problema, dovrei provarlo ma non ne ho il tempo stanotte. Se non trovi la soluzione nel caso domani notte faccio una prova e vedo.