Use of SQL Exists reproduced

For example, in the Northwind database
There is a query for

SELECT    C.customerid,   CompanyName    from   Customers   c    WHERE    EXISTS(                SELECT    OrderID from   Orders   o                WHERE   o.customerid   =   cu. CustomerID)   

How does this exists work? The subquery returns the OrderID field, but the outside query is looking for the CustomerID and CompanyName fields, and the two fields are definitely not in OrderID, how does this match?

exists is used to check if a subquery returns at least one row of data, and the subquery does not actually return any data, but instead returns a value of TRUE or False

EXISTS

Specifies a subquery that detects the presence of a row.

Grammar

EXISTS subquery

Parameters

Subquery

is a restricted SELECT statement (the COMPUTE clause and the INTO keyword are not allowed). For more information, see the discussion of subqueries in SELECT.

Result type

Boolean

Result values

Returns TRUE if the subquery contains rows.

Example

A. Using NULL in a subquery still returns the result set

This example specifies NULL in the subquery and returns the result set, which is still evaluated to TRUE by using EXISTS.

 Use Northwind GO SELECT CategoryName  from Categories WHERE EXISTS (SELECTNULL) ORDER  by ASC GO

B. Comparing queries using EXISTS and in

This example compares two semantically similar queries. The first query uses EXISTS and the second query uses in. Note Two queries return the same information.

 UsepubsGOSELECT DISTINCTpub_name fromPublishersWHERE EXISTS    (SELECT *     fromtitlesWHEREpub_id=publishers.pub_id andType=\'business\')GO--or, using the IN clause: UsepubsGOSELECT distinctpub_name fromPublishersWHEREpub_idinch    (SELECTpub_id fromtitlesWHEREType=\'business\')GO

The following is the result set for either query:

Pub_name
----------------------------------------
Algodatainfosystems
New Moonbooks

(2 row (s) affected)

C. Comparing queries that use EXISTS and = any

This example shows two query methods for finding authors who live in the same city as the Publisher: The first method uses = Any, and the second method uses EXISTS. Note Both of these methods return the same information.

 UsepubsGOSELECTau_lname, au_fname fromauthorsWHERE exists    (SELECT *     fromPublishersWHEREAuthors.city=publishers.city)GO--Or, using = any UsepubsGOSELECTau_lname, au_fname fromauthorsWHERECity=  any    (SELECT City fromPublishers)GO

The following is the result set for either query:

au_lname au_fname
---------------------------------------- --------------------
Carson Cheryl
Bennet Abraham

(2 row (s) affected)

D. Comparing queries using EXISTS and in

The query in this example looks for titles published by any publisher in a city that begins with the letter B:

 UsepubsGOSELECTtitle fromtitlesWHERE EXISTS    (SELECT *     fromPublishersWHEREpub_id=titles.pub_id andCity like\'b%\')GO--Or, using in: UsepubsGOSELECTtitle fromtitlesWHEREpub_idinch    (SELECTpub_id fromPublishersWHERECity like\'b%\')GO

The following is the result set for either query:

Title
------------------------------------------------------------------------
The Busy executive\ ' s databaseguide
Cooking with Computers:surreptitious balancesheets
You Can Combat computerstress!
Straight Talk Aboutcomputers
But is It userfriendly?
Secrets of Siliconvalley
Netetiquette
Is Anger Theenemy?
Life without Fear
Prolonged Data Deprivation:four casestudies
Emotional security:a Newalgorithm

(one row (s) affected)

E. Using not EXISTS

The role of not EXISTS is opposite to EXISTS. If the subquery does not return rows, the WHERE clause in not EXISTS is satisfied. This example finds the name of the publisher who does not publish a business book:

 UsepubsGOSELECTpub_name fromPublishersWHERE  not EXISTS    (SELECT *     fromtitlesWHEREpub_id=publishers.pub_id andType=\'business\')ORDER  bypub_nameGO

Here is the result set:

Pub_name
----------------------------------------
Binnet &hardley
Five lakespublishing
Ggg&g
Lucerne Publishing
Ramonapublishers
Scootneybooks

(6 row (s) affected)

Use of SQL Exists reproduced