Use: exists (TRANSACT-SQL)

Specify a subquery to test whether the row exists.

Transact-SQL syntax conventions

Syntax

Exists subquery

Parameters

Subquery

Restricted select statements. The compute clause and the into keyword are not allowed. For more information, see "select (TRANSACT-SQL)" for information on subqueries.

Result type

Boolean

Result Value

Returns true if the subquery contains any rows.

Example
A. using NULL in the subquery still returns the result set

In the following exampleNullAnd returns true by using exists.

	Copy code
Use adventureworks; goselect distinct mentid, name from HumanResources. Department where exists (select null) order by name ASC;

B. Compare queries using exists and in

The following example compares two queries with the same semantics. Use the first queryExists, Used by the second queryIn.

	Copy code
Use adventureworks; goselect distinct C. firstname, C. lastname, E. extends mentidfrom person. contact C join HumanResources. employee eon e. contactid = C. contactid where exists (select * From HumanResources. department dwhere E. required mentid = D. using mentidand D. name = 'purchasing'); go

Use the following queryIn.

	Copy code
Use adventureworks; goselect distinct C. firstname, C. lastname, E. extends mentidfrom person. contact C join HumanResources. employee eon e. contactid = C. contactid where specify mentid in (select distinct mentid from HumanResources. department dwhere D. name = 'purchasing'); go

The following is the result set of any of the queries.

	copy Code
Firstname lastname too many tables ------------ Annette Hill 5 Arvind Rao 5 Ben Miller 5 Eric kurjan 5 Erin hagins 5 Frank pellow 5 fukiko ogisu 5 Gordon Hee 5 Linda Meisner 5 Mikael Sandberg 5 reinout Hillmann 5 Sheela word 5 (12 row (s) affected)

C. Compare queries using exists and = any

The following example shows two queries for stores with the same name as the supplier. Use the first queryExists, Used by the second query= Any.

	Copy code
Use adventureworks; goselect distinct S. namefrom sales. Store s where exists (select * from purchasing. Vendor vwhere S. Name = V. Name); go

Use the following query= Any.

	Copy code
Use adventureworks; goselect distinct S. namefrom sales. Store s where S. Name = any (select v. namefrom purchasing. Vendor V); go

D. Compare queries using exists and in

The following example showsPDepartment employees.

	Copy code
Use adventureworks; goselect C. firstname, C. lastname, E. titlefrom person. contact C join HumanResources. employee eon e. contactid = C. contactid where exists (select * From HumanResources. department dwhere E. required mentid = D. using mentidand D. name like 'P % '); go

Use the following queryIn.

	Copy code
Use adventureworks; goselect C. firstname, C. lastname, E. titlefrom person. contact C join HumanResources. employee eon e. contactid = C. contactid where initialize mentid in (select initialize mentidfrom HumanResources. departmentwhere name like 'P % '); go

E. Use not exists

Not exists is opposite to exists. If the subquery does not return rows, the where clause in not exists is satisfied. The following example shows that the name is not in the Department and the name isPStart with an employee.

	Copy code
Use adventureworks; goselect C. firstname, C. lastname, E. titlefrom person. contact C join HumanResources. employee eon e. contactid = C. contactid where not exists (select * From HumanResources. department dwhere E. required mentid = D. using mentidand D. name like 'P % ') order by lastname, firstnamego

The following is the result set:

copy Code

Firstname lastname title alert ------------ Syed Abbas Pacific sales manager Hazem Quality Assurance Manager Humberto your application using Pilar Ackerman Shipping & sorting ing ç OIS ajenstat Database Administrator Amy Albert ts European sales manager Sean Alexander Quality Assurance technician Pamela ansman-Wolfe Sal Es 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 principal Karen Berge document control assistant Andreas Berglund Quality Assurance technician Matthias Berndt Shipping & sorting ing clerk Jo berry janitor Jimmy Bischoff stoc Ker 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 Quality Assurance Technician Hao HR Chen administrative assistant Kevin chrisulis Network Administrator Pat Coleman janitor Stephen Conroy Network Manager Debra core application specialist Ovidiu Cr extends cium Sr. tool designer grant Culbertson HR administrative assistant Mary Dempsey marketing partition D's tool designer Terri duy VP Engineering Susan Eaton Stocker Terry gradient marketing specialist Gail Erickson design engineer Janice Galvin tool designer Mary Gibson marketing extends jossef goldberg de Sign engineer sariya MARKETING Marketing lead 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 internal control lead Christian maintenance supervisor vamsi kuppa Shipping & sorting ing clerk David Liu Accounts Manager Vidur Luthra recruiter Stuart MacRae janitor Diane margheim Research & Development Executive Martin benefits executive Gigi Matthew Research & Development Executive Mensa-Annan Sales Representative Ramesh executive application today Dylan Miller Research & Development managerlinda sales representative Barbara Moreland accountant Laura Norman Chief Financial Ficer Chris norred control specialist Jae Pak sales representative Wanda parks janitor Deborah Poe accounts retriable against RILs Stocker tsvi Reiter sales representative Sharon salavaria design engineer Sanchez Chief Executive Officer Jos? Saraiva sales representative Mike seamans accountant ashvini Sharma Network Administrator Janet sheerdigian Accounts Payable specialist candy spoon accounts retriable specialistmichael Sullivan Sr. design Engineer Dragan Tomic Accounts Payable {lynn =sales representative Rachel Valdez sales representative Garret vargar sales representative Ranjit varkey variable sales today Bryan Walton accounts variable able {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)