• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server EXISTS

SQL Server EXISTS

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the EXISTS operator in a SQL statement to evaluate a SELECT SUBQUERY.

What is EXISTS in SQL Server?

EXISTS is a logical operator which is used to evaluate a SELECT SUBQUERY to determine whether it returns any row of information from the table. The outcome of the operation is a Boolean value which is either TRUE (1) or FALSE (0). If the subquery returns a row the EXISTS operation succeeds and evaluates to TRUE and exits and vice versa. There is also a counter part to the EXISTS operator called NOT EXISTS which returns TRUE when the evaluated subquery does not fetch any rows from the table.

The EXISTS and NOT EXISTS operators are used very commonly with CORRELATED SUBQUERIES.

Operation

The EXISTS or NOT EXISTS operators are used to evaluate subqueries which are part of SELECT, INSERT, UPDATE, and DELETE statements. As mentioned above the EXISTS or NOT EXISTS operators do not return any resultset or records but only the Boolean values.

SQL Server EXISTS Syntax

The basic syntax of the EXISTS and NOT EXISTS operators can be represented as below.

Main_Query WHERE EXISTS (SELECT subquery);
Main_Query WHERE NOT EXISTS (SELECT subquery);

In this syntax,

  • Main_Query – the outer query containing the EXISTS/NOT EXISTS condition in the WHERE clause.
  • EXISTS – logical operator to evaluate a subquery positively.
  • NOT EXISTS – logical operator to evaluate a subquery negatively.
  • SELECT subquery – the SELECT query which is being evaluated for its applicability (i.e. whether it does or does not fetch data).

SQL Server EXISTS Examples

Let us understand both varieties of the operator with practical examples. Suppose we have an employees table containing employee details along with their department and manager id’s as below. We will use this sample table as the reference for the examples.

emp_idfirst_namelast_nameemp_salarydept_idmgr_id
1270ClaudiaCrawford520002211279
1271JeffGoldsmith500002211279
1272NigelKnight600002221281
1273JamesOrwell660002221288
1274CindySmith650002221288
1275NikiBailey550002231277
1276MikeMattis690002231277
1277AngeliaJackson590002231275
1278MarthaDeClarke550002231290
1279AlbertHemingway550002211288
Table: employees

1) SQL Server  EXISTS example

We can see from the above that some employee ids are also manager ids. This means that some of the employees are managers and manage the other employees. We might want to retrieve the list of such employees who are also managers. This is be achieved using EXISTS as done by the following query.

SELECT emp_id, first_name, last_name
FROM employees emp1
WHERE EXISTS
(SELECT emp_id FROM employees emp2 WHERE emp1.emp_id=emp2.mgr_id);

It will generate the following resultset of management grade employees.

emp_idfirst_namelast_name
1275NikiBailey
1277AngeliaJackson
1279AlbertHemingway

Explanation: Here the outer main query executes first and fetches a record from the table with the employee id in it. Then the inner subquery will execute to check whether outer table (aliased emp1) row employee id matches the manager id of any row of the inner table (aliased emp2). If it does then that employee with the corresponding emp_id is a manager and the EXISTS condition evaluates to TRUE and the row of information (i.e. record) is retained and added to the final resultset. Likewise, the process repeats itself for every row of information (i.e. record) from the table and the retained records form the resultset.

2)  SQL Server  NOT EXISTS example

The following example is the reverse of the above example and produces the reverse result with the help of the NOT EXISTS operator (i.e. it returns the list of employees who are not managers but individual contributors/workers).

SELECT emp_id, first_name, last_name
FROM employees emp1
WHERE NOT EXISTS
(SELECT emp_id FROM employees emp2 WHERE emp1.emp_id=emp2.mgr_id);

It will generate the following resultset of non-management grade employees.

emp_idfirst_namelast_name
1270ClaudiaCrawford
1271JeffGoldsmith
1272NigelKnight
1273JamesOrwell
1274CindySmith
1276MikeMattis
1278MarthaDeClarke

Explanation: Here too the outer main query executes first and fetches a record from the table with the employee id in it. Then the inner subquery will execute to check whether outer table (aliased emp1) row employee id matches the manager id of any row of the inner table (aliased emp2). If it does not then that employee with the corresponding emp_id is not a manager and the NOT EXISTS condition evaluates to TRUE and the row of information (i.e. record) is retained and added to the final resultset. Likewise, the process repeats itself for every row of information (i.e. record) from the table and the retained records form the resultset.

Was this tutorial helpful?
YesNo
« Previous: SQL Server EXCEPT
SQL Server GROUPING SET :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.