EXISTS is a T-SQL operator which can be used to test existence of a result in subquery. Based on that it can perform query or some task. Sql EXISTS performs so called “Existence test”.

SQL EXISTS Syntax

1) SQL Query WHERE

SELECT Columns
FROM dbo.Table
WHERE EXISTS
   (SELECT columns
    FROM Dbo.Table2
    WHERE Condition);

Be aware that the operator performs only the existence test. If the records in subquery exist, it will perform main query without any limit. Result of the query will be every record of the table. This can be avoided by editing syntax. More in examples.

2) SQL Procedure

IF EXISTS (SELECT 1 FROM dbo.Table)
PRINT 'Record exists'

If some record is the result of the subquery, main query or sequence of steps will commence

SQL EXISTS Operator Examples

EXISTS does not perform result limitation but only existence test, as was mentioned. If we would like to test if there is non-zero number of records in subquery based on requirement and subsequently we would like to apply this requirement on the main query, we should approach this case like so:

Example – lets have 2 tables in the sports equipment store database:

• Table DimProduct – here is located the list of products (606 in total) which are sold by our company. Ever product belongs to some product category
• Table DimProductSubcategory – we can find there the list of product categories (37 categories in total)

The task is to use the operator to find all products belonging to category “helmets”. Empty table will be the result if this category does not exist.

Solution

SELECT [EnglishProductName] AS [Product_Name], [ListPrice] AS [Price_USD]
FROM [dbo].[DimProduct]
WHERE EXISTS
     (SELECT 1
      FROM [dbo].[DimProductSubcategory]
      WHERE [ProductSubcategoryKey] = [dbo].[DimProduct].[ProductSubcategoryKey]
      AND [EnglishProductSubcategoryName] = 'helmets')

• Limitation of the main query to category “Helmets” is done in WHERE part by joining both tables. This actually creates INNER JOIN and applies limitation also to main query.
• “SELECT 1” is used for performance reasons
• Same effect would be achieved if we used SQL operator IN

It would be a mistake to write a query using SQL EXISTS like this:

SELECT [EnglishProductName] AS [Product_Name], [ListPrice] AS [Price_USD]
FROM [dbo].[DimProduct]
WHERE EXISTS
     (SELECT 1
      FROM [dbo].[DimProductSubcategory]
      WHERE [EnglishProductSubcategoryName] = 'helmets')

Result would be all records of the table dbo.Dim Product