Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use EXISTS in SQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

EXISTS

Specify a subquery to detect the existence of rows.

Grammar

EXISTS subquery

Parameters.

Subquery

Is a restricted SELECT statement (COMPUTE clauses and INTO keywords are not allowed). For more information, see the discussion of related subqueries in SELECT.

Result type

Boolean

Result value

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, still taking the value TRUE by using EXISTS.

USE NorthwindGOSELECT CategoryNameFROM CategoriesWHERE EXISTS (SELECT NULL) ORDER BY CategoryName ASCGOB. Compare queries that use EXISTS and IN

This example compares two queries with similar semantics. The first query uses EXISTS and the second query uses IN. Notice that both queries return the same information.

USE pubsGOSELECT DISTINCT pub_nameFROM publishersWHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =\ 'business\') GO-- Or, using the IN clause:USE pubsGOSELECT distinct pub_nameFROM publishersWHERE pub_id IN (SELECT pub_id FROM titles WHERE type =\ 'business\') GO

The following is the result set of any query:

Pub_name-Algodata Infosystems New Moon Books (2 row (s) affected) C. Compare 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 that both methods return the same information.

USE pubsGOSELECT au_lname, au_fnameFROM authorsWHERE exists (SELECT * FROM publishers WHERE authors.city = publishers.city) GO-- Or, using = ANYUSE pubsGOSELECT au_lname, au_fnameFROM authorsWHERE city = ANY (SELECT city FROM publishers) GO

The following is the result set of any query:

Au_lname au_fname-Carson Cheryl Bennet Abraham (2 row (s) affected) D. Compare queries that use EXISTS and IN

The query shown in this example finds the title of a book published by any publisher in a city that begins with the letter B.

USE pubsGOSELECT titleFROM titlesWHERE EXISTS (SELECT * FROM publishers WHERE pub_id = titles.pub_id AND city LIKE\'B%\') GO-- Or, using IN:USE pubsGOSELECT titleFROM titlesWHERE pub_id IN (SELECT pub_id FROM publishers WHERE city LIKE\'B%\') GO

The following is the result set of any query:

Title-The Busy Executive\'s Database Guide Cooking with Computers: Surreptitious Balance Sheets You Can Combat Computer Stress! Straight Talk About Computers But Is It User Friendly? Secrets of Silicon Valley Net Etiquette Is Anger the Enemy? Life Without Fear Prolonged Data Deprivation: Four Case Studies Emotional Security: A New Algorithm (11 row (s) affected) E. Use NOT EXISTS

The effect of NOT EXISTS is opposite to that of EXISTS. If the subquery does not return a row, the WHERE clause in NOT EXISTS is satisfied. This example looks for the name of a publisher who does not publish business books:

USE pubsGOSELECT pub_nameFROM publishersWHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =\ 'business\') ORDER BY pub_nameGO

Here is the result set:

Pub_name-Binnet & Hardley Five Lakes Publishing GGG&G Lucerne Publishing Ramona Publishers Scootney Books (6 row (s) affected) finished reading the above content. Do you have any further understanding of how to use EXISTS in SQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.