EXISTS (Transact-SQL)
SQL Server 2008
Spécifie une sous-requête pour déterminer l'existence ou non de lignes.
A. Utilisation de NULL dans une sous-requête pour retourner un ensemble de résultats
Cet exemple retourne un ensemble de résultats avec la valeur NULL spécifiée dans la sous-requête mais continue à donner TRUE du fait de la clause EXISTS.
USE AdventureWorks ; GO SELECT DepartmentID, Name FROM HumanResources.Department WHERE EXISTS (SELECT NULL) ORDER BY Name ASC ;
B. Comparaison de requêtes à l'aide de EXISTS et de IN
Cet exemple compare deux requêtes sémantiquement équivalentes. La première requête utilise EXISTS et la seconde IN.
USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO
La requête suivante utilise IN.
USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO
Voici l'ensemble de résultats pour ces deux requêtes.
FirstName LastName -------------------------------------------------- ---------- Barry Johnson David Johnson Willis Johnson (3 row(s) affected)
C. Comparaison de requêtes à l'aide de EXISTS et de = ANY
L'exemple suivant présente deux requêtes permettant de retrouver le nom commun à plusieurs revendeurs. La première requête utilise EXISTS et la seconde =ANY.
USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE EXISTS
(SELECT *
FROM Purchasing.Vendor AS v
WHERE s.Name = v.Name) ;
GO
La requête suivante utilise = ANY.
USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE s.Name = ANY
(SELECT v.Name
FROM Purchasing.Vendor AS v ) ;
GO
D. Comparaison de requêtes à l'aide de EXISTS et de IN
L'exemple suivant illustre des requêtes chargées de retrouver les employés travaillant dans les différentes divisions d'une entreprise et dont le nom commence par P.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE EXISTS
(SELECT *
FROM HumanResources.Department AS d
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.EmployeeID = edh.EmployeeID
AND d.Name LIKE 'P%');
GO
La requête suivante utilise IN.
USE AdventureWorks; GO SELECT c.FirstName, c.LastName, e.Title FROM Person.Contact AS c JOIN HumanResources.Employee AS e ON e.ContactID = c.ContactID JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.EmployeeID WHERE edh.DepartmentID IN (SELECT DepartmentID FROM HumanResources.Department WHERE Name LIKE 'P%'); GO
E. Utilisation de NOT EXISTS
NOT EXISTS fonctionne à l'inverse de EXISTS. La clause WHERE figurant dans NOT EXISTS est satisfaite si aucune ligne n'est retournée par la sous-requête. L'exemple suivant recense les employés qui ne travaillent pas dans les différentes divisions d'une entreprise et dont le nom commence par P.
SELECT c.FirstName, c.LastName, e.Title FROM Person.Contact AS c JOIN HumanResources.Employee AS e ON e.ContactID = c.ContactID WHERE NOT EXISTS (SELECT * FROM HumanResources.Department AS d JOIN HumanResources.EmployeeDepartmentHistory AS edh ON d.DepartmentID = edh.DepartmentID WHERE e.EmployeeID = edh.EmployeeID AND d.Name LIKE 'P%') ORDER BY LastName, FirstName GO
Voici l'ensemble des résultats.
FirstName LastName Title ------------------------------ ------------------------------ ------------ Syed Abbas Pacific Sales Manager Hazem Abolrous Quality Assurance Manager Humberto Acevedo Application Specialist Pilar Ackerman Shipping & Receiving Superviso François Ajenstat Database Administrator Amy Alberts European Sales Manager Sean Alexander Quality Assurance Technician Pamela Ansman-Wolfe Sales Representative Zainal Arifin Document Control Manager David Barber Assistant to CFO Paula Barreto de Mattos Human Resources Manager Shai Bassli Facilities Manager Wanida Benshoof Marketing Assistant Karen Berg Application Specialist Karen Berge Document Control Assistant Andreas Berglund Quality Assurance Technician Matthias Berndt Shipping & Receiving Clerk Jo Berry Janitor Jimmy Bischoff Stocker Michael Blythe Sales Representative David Bradley Marketing Manager Kevin Brown Marketing Assistant David Campbell Sales Representative Jason Carlson Information Services Manager Fernando Caro Sales Representative Sean Chai Document Control Assistant Sootha Charncherngkha Quality Assurance Technician Hao Chen HR Administrative Assistant Kevin Chrisulis Network Administrator Pat Coleman Janitor Stephanie Conroy Network Manager Debra Core Application Specialist Ovidiu Crãcium Sr. Tool Designer Grant Culbertson HR Administrative Assistant Mary Dempsey Marketing Assistant Thierry D'Hers Tool Designer Terri Duffy VP Engineering Susan Eaton Stocker Terry Eminhizer Marketing Specialist Gail Erickson Design Engineer Janice Galvin Tool Designer Mary Gibson Marketing Specialist Jossef Goldberg Design Engineer Sariya Harnpadoungsataya Marketing Specialist Mark Harrington Quality Assurance Technician Magnus Hedlund Facilities Assistant Shu Ito Sales Representative Stephen Jiang North American Sales Manager Willis Johnson Recruiter Brannon Jones Finance Manager Tengiz Kharatishvili Control Specialist Christian Kleinerman Maintenance Supervisor Vamsi Kuppa Shipping & Receiving Clerk David Liu Accounts Manager Vidur Luthra Recruiter Stuart Macrae Janitor Diane Margheim Research & Development Enginee Mindy Martin Benefits Specialist Gigi Matthew Research & Development Enginee Tete Mensa-Annan Sales Representative Ramesh Meyyappan Application Specialist Dylan Miller Research & Development Manager Linda Mitchell Sales Representative Barbara Moreland Accountant Laura Norman Chief Financial Officer Chris Norred Control Specialist Jae Pak Sales Representative Wanda Parks Janitor Deborah Poe Accounts Receivable Specialist Kim Ralls Stocker Tsvi Reiter Sales Representative Sharon Salavaria Design Engineer Ken Sanchez Chief Executive Officer José Saraiva Sales Representative Mike Seamans Accountant Ashvini Sharma Network Administrator Janet Sheperdigian Accounts Payable Specialist Candy Spoon Accounts Receivable Specialist Michael Sullivan Sr. Design Engineer Dragan Tomic Accounts Payable Specialist Lynn Tsoflias Sales Representative Rachel Valdez Sales Representative Garrett Vargar Sales Representative Ranjit Varkey Chudukatil Sales Representative Bryan Walton Accounts Receivable Specialist Jian Shuo Wang Engineering Manager Brian Welcker VP Sales Jill Williams Marketing Specialist Dan Wilson Database Administrator John Wood Marketing Specialist Peng Wu Quality Assurance Supervisor (91 row(s) affected)
