Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Operator RSS Feeds

Exists Operator

Posted By: Sarita Patel     Category: SQL Server     Views: 3759

This article explains the use of exists operator with different examples.

EXISTS operator checks for the existence of any rows with matched values in the subquery. The subquery could query the same table, or different tables, or a combination of both. It is possible to specify nonempty list of values with the Exists operator, it would always return TRUE.

 

Syntax of Exists Operator :

EXISTS subquery


Subquery is a restricted SELECT statement, the COMPUTE clause and the INTO keyword are not allowed.

Return type of exists is boolean. It returns TRUE if subquery contains any rows.  



Examples of Exists Operator :


Example 1 : Using exists operator in where clause

SELECT P.ProductID, P.ProductName

FROM   Products P

WHERE  EXISTS( SELECT * FROM Sales_by_Category S

                                 WHERE P.ProductName = S.ProductName)

ORDER BY P.ProductName


Output

ProductName                    UnitPrice

Boston Crab Meat                18.40

Gnocchi di nonna Alice       38.00

Gudbrandsdalsost                36.00

Outback Lager                     15.00

Schoggi Schokolade                        43.90

Thüringer Rostbratwurst   123.79

Tourtière                               7.45


Above query shows use of exists operator to get products and unitprice which are sold. You can also achieve same result using IN and = condition with ANY operator. To view this example, please visit below links.

IN Operator

ANY Operator



Example 2 : Using Not Exists Operator in where clause

SELECT P.ProductID, P.ProductName

FROM   Products P

WHERE NOT EXISTS(SELECT * FROM Sales_by_Category S

                              WHERE P.ProductName = S.ProductName)

ORDER BY P.ProductName

 

Output

ProductName             UnitPrice

Alice Mutton                39.00

Aniseed Syrup              10.00

Camembert Pierrot      34.00

Carnarvon Tigers          62.50

Chai                               18.00

 

Above query shows use of NotExists operator to get products and unitprice which are not sold.

  
Share: 

 
 
 

Didn't find what you were looking for? Find more on Exists Operator Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of Exists Operator is from United States.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!