SQL SERVER EXIST OR NOT EXIST CLAUSE EXAMPLE

 

SQL Server EXISTS Clause with Example and Result

The EXISTS clause in SQL Server is used to check whether a subquery returns any rows.

If the subquery returns at least one row, EXISTS returns TRUE.

---

Syntax

SELECT column_name
FROM table_name
WHERE EXISTS
(
    SELECT column_name
    FROM table_name
    WHERE condition
);

---

Example

Step 1: Create Tables

CREATE TABLE Customers
(
    CustomerID INT,
    CustomerName VARCHAR(50)
);

CREATE TABLE Orders
(
    OrderID INT,
    CustomerID INT,
    ProductName VARCHAR(50)
);

---

Step 2: Insert Data

INSERT INTO Customers VALUES
(1, 'Rahul'),
(2, 'Amit'),
(3, 'Neha'),
(4, 'Priya');

INSERT INTO Orders VALUES
(101, 1, 'Laptop'),
(102, 1, 'Mouse'),
(103, 3, 'Keyboard');

---

Step 3: Use EXISTS Clause

SELECT CustomerID, CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT *
    FROM Orders O
    WHERE O.CustomerID = C.CustomerID
);

---

Result

CustomerID	CustomerName
1	Rahul
3	Neha

---

Explanation

• Rahul has orders in the Orders table.
• Neha also has orders.
• Amit and Priya do not have any orders, so they are not displayed.

---

Example with NOT EXISTS

Display customers who have not placed any orders.

SELECT CustomerID, CustomerName
FROM Customers C
WHERE NOT EXISTS
(
    SELECT *
    FROM Orders O
    WHERE O.CustomerID = C.CustomerID
);

---

Result

CustomerID	CustomerName
2	Amit
4	Priya

---

Key Points

• EXISTS checks for matching rows.
• Mostly used with subqueries.
• Faster than IN in many large-data cases.
• NOT EXISTS is used for unmatched records.