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.