EXISTS and NOT EXISTS

Test existence without needing the related values.

UnderstandStudy an examplePractise

Understand the concept

EXISTS is TRUE when its subquery produces at least one row. The projected value is not the point; SELECT 1 is a common convention. NOT EXISTS tests the absence of matching rows.

For customers who have ordered, EXISTS states the intention more directly than multiplying rows with a JOIN and removing duplicates. NOT EXISTS avoids the NULL problem of NOT IN. Do not declare one pattern universally faster: inspect the plan, indexes and data distribution.

WHEN TO USE IT

Use it for existence, absence, unsold products and missing relationships.

COMMON PITFALL

Assuming the value projected inside EXISTS determines the match.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT c.FullName FROM lab.Customer AS c WHERE EXISTS (SELECT 1 FROM lab.SalesOrder AS o WHERE o.CustomerId = c.CustomerId);

Finds five customers with orders, without repeating customers.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Find customers without orders using NOT EXISTS.

Show the solution and explanation

Worked solution

SELECT c.FullName FROM lab.Customer AS c WHERE NOT EXISTS (SELECT 1 FROM lab.SalesOrder AS o WHERE o.CustomerId = c.CustomerId);

Selin. The subquery projection does not change the existence test.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.