SQL EXISTS – Exploring EXISTS Operator in SQL Server

SQL Exists is a conditional operator that checks the existence of rows in a subquery. It returns true if the subquery returns at least one row and false if it returns no rows.

SQL Exists is often used in conjunction with other conditional operators such as WHERE, HAVING, and SELECT.

In this article, we will explore the basics of SQL Exists and show you how to use it in your queries. We will also use some examples and best practices for working with SQL Exists.

sql-exists-operator
SQL Exists

Syntax of SQL Exists:

The basic syntax of SQL Exists is as follows:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

The subquery can be a SELECT statement, a table name, or a combination of both.

Using SQL Exists with Subqueries

SQL Exists can be used with subqueries to filter data based on specific conditions. The syntax for using SQL Exists with subqueries is as follows:

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

Using SQL Exists with Joins

SQL Exists can also be used with joins to filter data from multiple tables based on specific conditions. The syntax for using SQL Exists with joins is as follows:

SELECT column_name(s)
FROM table_name1
WHERE EXISTS (SELECT column_name(s) FROM table_name2 WHERE condition)
AND table_name1.column_name = table_name2.column_name;

Here is an example of using SQL EXISTS with joins on the “orders” and “customers” tables in SQL Server:

Table orders:

idcustomer_idpriceorder_date
110011002023-01-01
21002502023-02-02
3100399.992023-02-03
4100199.992023-01-04
51004102023-01-05

Table customers:

idnameemail
1001Shekh Alishekh.ali@gmail.com
1002Miller Doemiller.doe@gmail.com
1003Robert Shawrobert.shaw@gmail.com
1004Susan Leesusan.lee@gmail.com
1005Tom Kimtom.kim@gmail.com

Suppose we want to find all customers who have placed an order in January 2023. We can use SQL EXISTS with an inner join to achieve this:

SELECT DISTINCT c.name, c.email, o.price, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE EXISTS (
  SELECT 1
  FROM orders o2
  WHERE o2.customer_id = c.id
    AND o2.order_date >= '2023-01-01'
    AND o2.order_date <= '2023-01-31'
);

Output:

sql-exists-in-joins
SQL EXISTS in JOINS

SQL NOT EXISTS:

We can use the NOT operator to reverse the effect of the EXISTS clause.

Here, The CREATE TABLE statement is used to create a new table in a database. However, an error will occur if a table with the same name already exists. To avoid this, you the following SQL Server statement, which checks if the table already exists before attempting to create it.

Here is an example of using NOT operator with EXISTS in SQL Server:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[customers]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[customers] (
        id INT NOT NULL,
        name VARCHAR(50) NOT NULL,
        email VARCHAR(50) NOT NULL,
        PRIMARY KEY (id)
    );
END;

The above SQL Server query first checks if a table named “customers” already exists in the database using the sys.objects system view. If the table does not exist, it creates the “customers” table with the specified columns and primary key constraints.

DROP TABLE IF EXISTS


This statement will drop the “Orders” table if it exists, but it will not produce an error if it doesn’t.

DROP TABLE IF EXISTS ORDERS;
-- or
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[orders]') AND type in (N'U'))
BEGIN
    DROP TABLE ORDERS;
END

Here are some examples of SQL EXISTS Operator:

01. Check if any customers have placed an order:

SELECT *
FROM customers
WHERE EXISTS (
SELECT *
FROM orders
WHERE orders.customer_id = customers.id
);

This query will return all rows from the “customers” table where there exists a matching row in the “orders” table with the same customer ID. In other words, it will return all customers who have placed an order.

Output:

idnameemail
1001Shekh Alishekh.ali@gmail.com
1002Miller Doemiller.doe@gmail.com
1003Robert Shawrobert.shaw@gmail.com
1004Susan Leesusan.lee@gmail.com

02. Check if any customers have not placed an order:

SELECT *
FROM customers
WHERE NOT EXISTS (
SELECT *
FROM orders
WHERE orders.customer_id = customers.id
);

This query will return all rows from the “customers” table where a matching row does not exist in the “orders” table with the same customer ID. In other words, it will return all customers who have yet to place an order.

Output:

idnameemail
1005Tom Kimtom.kim@gmail.com

03. Check if there are any orders with a total price greater than $50:

SELECT id,price
FROM orders
WHERE EXISTS (
SELECT *
FROM orders AS Order2
WHERE orders.id = Order2.id and orders.price > 50
);

Output:

idprice
1100
399.99
499.99

FAQs

Q: What is the purpose of SQL EXISTS in SQL Server?

SQL EXISTS is used to check if a subquery returns any rows. It is often used in combination with the WHERE clause to filter results based on the existence of related data in another table.

Q: How is SQL EXISTS different from SQL IN?

SQL EXISTS and SQL IN are both used to filter results based on values in another table.
However, SQL EXISTS is more efficient for large datasets because it only needs to check if the subquery returns any rows, while SQL IN needs to match each value in the main query to a value in the subquery.

Q: Can you use SQL EXISTS with multiple tables?

Yes, you can use SQL EXISTS with multiple tables by including additional subqueries in the WHERE clause.
For example:

SELECT *
FROM customers
WHERE EXISTS (
SELECT *
FROM orders
WHERE orders.customer_id = customers.id
)
AND EXISTS (
SELECT *
FROM payments
WHERE payments.customer_id = customers.id
);

References: MSDN-SQL EXISTS

You might want to read this too:

Let others know about this post by sharing it and leaving your thoughts in the comments section.

Shekh Ali
4 1 vote
Article Rating
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments