SQL EXISTS Operator With Exampes – Existence Test

Last modified date:

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”.

Syntax

1) SQL Query

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')

sql exists operator - example result

  • 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

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SQL Operators

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.