Grab Deal : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

- SQL Server Blogs -

How To Use The SQL NOT EXISTS and EXISTS Operator ?


Understanding SQL EXISTS and NOT EXISTS Operators

Even though SQL EXISTS and NOT EXISTS operators have been around since the beginning, many find them confusing. This is because they leverage subqueries, which are powerful tools for filtering data based on conditions in another table.

What they do:

  • EXISTS: Checks if at least one record exists based on the subquery's condition. If the subquery returns any rows (one or more), EXISTS evaluates to TRUE.
  • NOT EXISTS: The opposite of EXISTS. It's satisfied only if the subquery returns zero rows. In other words, if there are no matching records in the subquery, NOT EXISTS evaluates to TRUE.

Benefits of using them:

  • Flexibility: They allow for complex filtering logic based on data in different tables.
  • Performance: In some cases, they can optimize queries by stopping the subquery execution as soon as a matching record is found (EXISTS) or no records are found (NOT EXISTS).

This article will cover:

  • The purpose of EXISTS and NOT EXISTS operators
  • Subqueries explained
  • Basic syntax for using these operators
  • Examples to illustrate their functionality
  • Performance considerations when using them

Let's dive deeper and explore these concepts in detail!

SQL Not Exist and Exist Operator

Understanding SQL EXISTS and NOT EXISTS Operators

SQL offers operators like EXISTS and NOT EXISTS to check for the existence of records based on a subquery. A subquery is essentially a mini-query nested within another query. It retrieves data used to filter the main query's results.

What they do:

  • EXISTS: This operator checks if the subquery returns at least one record. If the subquery finds any rows, EXISTS evaluates to TRUE.
  • NOT EXISTS: The opposite of EXISTS. It's satisfied only if the subquery returns zero rows. In other words, if there are no matching records in the subquery, NOT EXISTS evaluates to TRUE.

Similarities and Differences with IN Operator:

The EXISTS operator can achieve similar results to the IN operator in some cases, but their syntax differs. EXISTS uses a subquery, while IN takes a list of values for comparison.


These operators can be used with SELECT, UPDATE, or DELETE statements to filter data based on subquery results.

Key Points:

  • EXISTS helps find rows where a related record exists based on the subquery.
  • NOT EXISTS helps find rows where there are no related records based on the subquery.
  • They offer flexibility in filtering data across tables.

Next Steps:

This article will provide examples to illustrate how EXISTS and NOT EXISTS work in practice. We'll also explore considerations for using them effectively in your queries.

Note: The text about the "WITH" clause and CTEs is not directly related to EXISTS and NOT EXISTS and can be omitted for a more focused explanation.


Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

What is a Subquery in SQL?

A subquery is also named as the inner query or the nested query that is frequently used within other queries. The SQL subquery can be nested with multiple statements like SELECT, INSERT, UPDATE, or DELETE statements, etc. Mostly, we use a subquery in SQL with Where and EXISTS clauses.

 Here are some basic rules for using subqueries in SQL:

  • Each subquery should be enclosed within parentheses.
  • We cannot use Order By clause within the subquery until we don’t use the TOP clause.
  • Each subquery should include the regular SELECT statement.
  • The Having, Where, Group By clauses can be used optionally within a subquery.
  • You cannot use the Compute clause within a subquery.
  • A subquery is generally nested inside WHERE and Has clause or outer statements like SELECT, INSERT, UPDATE, or DELETE, etc.
  • SQL server allows you to nest subqueries up to 32 levels. It may vary based on requirements.

Sign Up for JanBaskTraining certification courses to understand the real-time application of SQL.

Now let’s understand the basic syntax of the SQL NOT EXISTS and EXISTS operator.

The Basic Syntax of the SQL EXISTS Operator

SELECT column_name(s)
FROM table_name
(SELECT column_name(s)
FROM table_name
WHERE condition);
  • Here, you have to give the column name of the table.
  • Expressions can be a single constant, variable, scalar function, the column name, or the pieces of a SQL query that compares values and perform arithmetic calculations when required.
  • The name should be added in the second line of the code.
  • The last line of code checks the existence of one or more rows in a subquery. When a single row satisfies the condition, it returns the Boolean value True. If there are no matching rows then it returns the Boolean value False otherwise you can use the “NOT EXISTS” operator too.

Look for more details on what does Sql stand for? Read our comprehensive guide now!

The Basic Syntax of the SQL NOT EXISTS in SQL

NOT EXISTS (subquery);
  • You can choose the number of columns from the table.
  • The SQL JOINS are used to join multiple tables.
  • If the subquery returns true then it will return the records true, else it won’t return any records.
  • Read our comprehensive blog to understand the difference between View & Table In SQL.

Let’s check out some examples of SQL EXISTS Operator.


SQL NOT EXISTS And Exists Operator Example

Below is an example to help you better understand how to use SQL NOT EXIST and EXIST Operator.

SQL NOT EXISTS codeImage source: MySQL

The last example is a double-nested NOT EXISTS query. Which means, it has a NOT EXISTS clause within a NOT EXISTS clause. Formally, it answers the question “does a city exist with a store that is not in Stores”? But it is easier to say that a nested NOT EXISTS answers the question “is x TRUE for all y?

Let’s know how you can use EXISTS condition with SELECT, NOT, DELETE,  and UPDATE statements.

Using EXISTS condition with SELECT statement

To get the first and last name of the customers who placed at least one order.

SELECT fname, Iname FROM Customers
FROM Orders
WHERE Customers.customer_id = Orders.c_id);

Using NOT with EXISTS

This Query will help to get the last and first name of the customers who have not placed any order.

SELECT Iname, fname FROM Customer
FROM Orders
WHERE Customers.customer_id = Orders.c_id);

Using EXISTS condition with DELETE statement

To delete the record of all the customers from the Order Table whose last name is ‘xyz’’.

FROM Orders
FROM customers
WHERE Customers.customer_id = Orders.cid
AND Customers.Iname = 'XYZ');

Using EXISTS condition with UPDATE statement,

Update the Iname as 'XYZ' in Customer Table whose customer id is 301.
UPDATE Customers
SET Iname = 'XYZ'
FROM Customers
WHERE customer_id = 401);

The “IN” operator in SQL

The IN operator can also be used to get the same output. But the question is, which operator is more suitable for different situations? If the subquery returns a very large record-set, the EXISTS operator works great here. However, if the record-set is smaller then you must use the IN operator.

EXISTS Vs Count Operators in SQL

Count operator is used to check the total number of rows within a table while EXISTS operator is used to check the existence of rows when a particular condition is satisfied. In simple words, “EXISTS” operator may short-circuit after having found the first matching row while “Count” is not allowed to show such type of behavior.

The SQL database works slightly better when using the EXISTS operator instead of the Count operator because of a much better cardinality estimate in the middle of an execution plan. Every time you want to check a row for existence, you should always use the EXISTS operator in that case. Also, the associated costs with the EXISTS operator are almost 30 times less than the Count operator.

“It is obvious to check the existence faster rather than to count all rows together if you are really doing something for the existence only.

Performance Guidelines with EXISTS Operator

Every SQL operator makes the difference to improve the performance of a query. An SQL operator works with data items and returns the result. Oracle uses operators under different conditions that can be used with queries to filter the result set. In the same EXISTS operator ensure the best performance when the query contains voluminous data to evaluate. 

The reason is that the EXISTS operator follows the “at least found” principle in queries. It is set to TRUE if even a single row satisfies the condition. In some cases, it stops the execution at the same place where the result is set to TRUE. However, you should use comparison operators with EXISTS to continue the table scanning ahead.

Are you interested in learning about SQL Schema? Read our blog and learn how to create, alter and drop a schema in SQL.

Performance Guidelines with SQL NOT EXIST Operator

  • Most DBAs prefer to use the NOT EXISTS clause between NOT EXISTS and NOT IN operator.
  • When SQL includes a NOT IN clause, a subquery is generally used.
  •   With NOT EXISTS, a correlated subquery is used. 
  • In many cases a NOT IN will produce the same execution plan as a NOT EXISTS query or a not equal query (!=).
  • In some cases a correlated NOT EXISTS subquery can be re-written with a standard outer join with a NOT NULL test.
  • Some NOT EXISTS subqueries can be tuned using the MINUS operator to tune a NOT EXISTS.

Do you want to grow your career as an SQL Developer? Give a competitive edge to your SQL Career Path by enrolling in our SQL Certification Courses.


With this discussion, we come to the end of the blog. Here, you know the basics of SQL EXISTS operator, all about SQL not existing and how to use it with a SQL query. We have discussed one detailed example too for your reference to understand the concept in depth. But one example is not enough to practice this operator. 

To learn multiple SQL NOT EXIST and EXISTS examples, you should join the online SQL certification training right away and start practicing different operators quickly. It will help you learn SQL practically and become a SQL professional with attractive salary packages. 

Join our JanBask Training SQL - Server community and get access of better networking opportunities.

Register for our Demo class today for more details!

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Frequently Asked Questions

Q1) . Where NOT EXISTS SQL is used?

Ans:- NOT EXISTS SQL means nothing returned by the subquery. It is used to restrict the number of rows returned by the SELECT statement. In the server, it checks the Subquery for row existence, and if there are no browns then it will return true otherwise false. 

Q2). How do you avoid NOT EXISTS in SQL?

Ans:- To resolve the SQL server NOT EXISTS issue, make sure you are searching on indexed. Make sure, there should be no manipulation of the data within those columns. columns. Now, replace the No EXISTS with a left outer join, it works better in the large data sats. 

Q3). How do you check if record NOT EXISTS in SQL?

Ans:- There are different ways to check if record NOT EXIST in SQL, here are few:

  • With the use of the EXISTS clause in the IF statement. 
  • By using EXISTS  clause in the case statement to check
  • “And the last through EXISTS clause in the where clause to check

Q4). What is NOT in SQL?

Ans:- Not in SQL operator is used when the user is looking to retrieve a column, which has no entries in the table or referencing table. A customer table contains the records of all the customers and the transaction table tracks the transaction between the store and the customer. 

Q5). How does SQL NOT EXISTS work?

Ans:- The SQL NOT EXISTS command is used to check the existence of particular values in the given subquery. The subquery does not return any data, it just returns True or False values depending on the subquery values existence check. 

Q6). How would you use it and exist in SQL?

Ans:- With the SQL Not in command, you can specify numerous values in the Where clause. While the SQL NOT EXISTS command is helpful in checking for the existence of specific values in the given subquery. 

Q7).  How do you create table if NOT EXISTS in SQL?

Ans:- To create if NOT EXISTS SQL table, first, specify the name of the table that you are looking to create after the Create Table Keywords. Second, use the if NOT EXISTS SQL option to create a new table if it does not exist. Now,  if you want to, you can specify the schema name to which the new table belongs and at last, specify the column list of the table. 

Q8). Why do we use if EXISTS in SQL?

Ans:- SQL if EXISTS is used to test for the existence of any record in a subquery. The EXISTS operator returns true if the subquery returns one or more records. 

Q9). What to use instead of SQL NOT EXISTS?

Ans:- A good alternative of In and EXISTS is an Inner Join, which is a Left Outer Join with Where clause checking for Null values can be used as an alternative for Not In and NOT EXISTS. 

Q10). How do you use EXISTS in SQL instead of in?

Ans:- The EXISTS keyword is responsible for checking true or false while, In keyword does comparison of all values in the corresponding sub query column. 

SQL Tutorial Overview


    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.

  • fb-15
  • twitter-15
  • linkedin-15


  • A

    Arlo Hill

    One of the best posts I have ever come across on SQL. Not only did I learn a lot about this language but also it took me hardly five minutes to go through the whole post.

  • T

    Tobias Scott

    Another epic post! I was looking for such an informative post to enhance my knowledge in SQL, thank god I could find this post. It is really awesome. Thank you so much for sharing it!

    • logo16


      find this post. It is really awesome. Thank you so much for sharing it!

  • B

    Brady Green

    Thank you for this interesting post. Most people still do not understand how significant learning different coding languages is, especially SQL. I really like all this information and tips. Wish you all the best!

    • logo16


      Glad to hear that you found this post valuable. Often visit our website to read more such posts.

  • C

    Clayton Adams

    Impressive, I must say. I have been reading your blogs for a long time to increase my knowledge. Looking forward to your next post.

    • logo16


      Thanks a lot for your valuable comment, always visit our site to read more interesting content.

  • F

    Francisco Baker

    This is such a comprehensive post on SQL including all the major facts which I need to check for having a good knowledge of SQL. Please bring such more informative articles like this which can help us to grow our knowledge.

    • logo16


      Thanks a lot for such a positive comment, we will surely bring such amazing posts.

  • W

    Walter Carter

    Hi, It is really amazing how you have scripted the complete post. I actually had to stop reading and start applying your tips to resolve the SQL errors! Thank you!

    • logo16


      Aha! This is really very motivating to hear. Hope, these tips will help you in your growth.

  • E

    Erick Nelson

    Interesting post! All these points and facts related to SQL are quite helpful to prepare for the interview. I will make sure to bookmark this post for future use, thank you!

  • D

    Daxton Mitchell

    It is really nice to see how smoothly you have explained such a complex topic in an easy way and even without making it boring. Heads Up to you!

    • logo16


      Thank you so much for these motivating words, we are glad to hear them from you.

  • C

    Cash Perez

    Great stuff, learnt a lot from this blog also, as always. Thanks a lot for sharing such amazing and informative content!

    • logo16


      Sure, we will soon come up with a new topic with a lot of information and interesting stuff.

  • D

    Damien Turner

    Oh, such a mind-blowing post! Learned a lot about SQL language . I am very much looking forward to your next post. Keep sharing such content.

    • logo16


      You are most welcome. Keep coming back to our website for more insightful posts.

Related Courses

Trending Courses


Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models

Upcoming Class

5 days 31 May 2024



  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

3 days 29 May 2024



  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

2 days 28 May 2024


Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

7 days 02 Jun 2024


MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

5 days 31 May 2024


Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

12 days 07 Jun 2024



  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

-1 day 25 May 2024



  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 01 Jun 2024



  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

-1 day 25 May 2024


Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

13 days 08 Jun 2024


Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

5 days 31 May 2024



  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

6 days 01 Jun 2024