Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

How To Use The Exists Operator In The SQL?

The purpose of the SQL “Exists” and “Not Exists” operator is to check the existence of records in a subquery. One more similar operator is “with” clause that was introduced in 1999 to support the CTE (Common Table Expressions) features. These operators are predefined in the SQL and used along UPDATE, DELETE, or SELECT statements. The SQL “Exists” and  “Not Exists” operators must be used together because they are not independent by themselves and returns boolean values either True or False mutually.

When SQL Exists is used along Where clause, it tests the existence of rows in a subquery.

  • If that subquery contains a row then it returns the TRUE.
  • Exists operator is similar to IN operator in SQL and their output is almost the same but the syntax is different.
  • It is possible using Exists operator with UPDATE, DELETE, or SELECT statements.

The basic syntax of SQL Exists operator is given below.

SQL Exists operator

  • Here, you have to give the column name of the table.
  • Expressions can be single constant, variable, scalar function, the column name, or the pieces of a SQL query that compares values and perform arithmetic calculations when required.
  • In the second line of code, you should add the table name.
  • 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 “Not Exists” operator too.

The subquery word has been used multiple times till the time. Do you actually know the meaning of subquery in the SQL? If not, don’t worry. We will discuss in brief what is a SQL subquery before going to deep further. SQL Server Curriculum

Read: How to Add A New Column to a Table in SQL?

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 parenthesis.
  • 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 Compute clause within a subquery.
  • A subquery is generally nested inside WHERE and Having clause or outer statements like SELECT, INSERT, UPDATE, or DELETE etc.
  • SQL server allows you nesting subqueries up to 32 levels. It may vary based on requirements.

SQL “Exists” Operator with Example

To understand the SQL “Exists” operator in depth, let us discuss an example below. There are two tables where one table stores the Employee details and the second tables stores the employees’ salary paid monthly. Let us see how to use “Exists” operator with these two tables. SQL “Exists” Operator with Example SQL “Exists” Operator with Example You can see three sets of dummy data in the table. Here, the first table stores the employee details, second table stores the salary paid to employees, and the third table stores the result for “Exists” query. In the third table, you can see that five records are returned in total and each of them has a unique employee ID that is compared together for both tables.

If there is some ID that does not exist in both tables then the result will be FALSE in that case. For example, “6” ID is given in the first table but not mentioned in the second table, so it is not displayed as the output in the Exists table.

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 sub-query returns a very large record-set then “Exists” operator works great here. However, if the record-set is smaller then you must use IN operator in that case. SQL Server quiz

Read: How to Clear SQL Server Transaction Log File with DBCC Shrinkfile

Not Exists” operator in SQL

The “Not Exists” operator works opposite to the Exists operator. It means that no rows are returns when “Not Exists” operator is satisfied. Let us understand the concept with the help of the same example given earlier. “Not Exists” operator in SQL The output here will be the opposite. “Not Exists” operator in SQL In the Exists table above, these two records were omitted and to retrieve these two rows, you should use “Not Exists” operator. The same output is given by the “Not IN” operator in SQL as it works opposite to the “IN” operator.

Exists Vs Count Operators in SQL

“Count” operator is used to checking the total number of rows within a table while “Exists” operator is used to checking 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 “Exists” operator instead of “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 the existence, you should always use “Exists” operator in that case. Also, the associated costs with “Exists” operator are almost 30 times less than the “Count” operator.

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

Read: SSRS Tutorial for Beginners

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. free SQL Server demo The reason is “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 case, 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.

Conclusion

When you know the basics of SQL “Exists” operator and how to use it with a SQL query, it is the right time to practice more and more. We have discussed one detailed example in the blog for your reference to understand the concept in depth. But one example is not enough to practice this operator.

To help yourself with a detailed and structured approach, join the online SQL certification program and start practicing different operators quickly. It will help you to learn practical aspects of SQL and make you ready for different challenges to face in a job environment.

Read: What is SQL Subquery? Types of Subqueries in SQL

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

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

Upcoming Class

5 days 25 Nov 2019

Data Science

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

Upcoming Class

5 days 25 Nov 2019

Hadoop

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

Upcoming Class

6 days 26 Nov 2019

Salesforce

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

Upcoming Class

14 days 04 Dec 2019

Course for testing

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

Upcoming Class

34 days 24 Dec 2019

QA

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

Upcoming Class

13 days 03 Dec 2019

Business Analyst

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

Upcoming Class

5 days 25 Nov 2019

SQL Server

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

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews