SQL Exists Statement

SQL Exists statement

 

SQL or sequence query language is a mechanism that we use to interact with the database.

 

SQL Exists statement specifies a subquery to test for the existence of row(s), or in other words, the SQL Exists statement is used to test for the existence of any record in a subquery. 

 

The SQL Exists statement returns true if the subquery returns one or more records.

 

SQL Exists statement is a restricted Select statement The "into" Keyword is not allowed to be used the SQL Exists statement. 

 

Syntax 

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

Note 

• A sample database has been attached for your reference. You can download it, or can create your own database.
• I am using Microsoft SQL Server 2019, you can use any other SQL providers.  

How to use Null statement in a subquery to still return a result set   

 

The following example returns a result set with Null specified in the subquery and which evaluates to true by using Exists statement. You can see that although we are using Null, we are still getting an output. 

 

Syntax 

--Uses sample
SELECT OrderID,OrderName, orderAddress From OrderDetail
Where EXISTS (SELECT NULL)
ORDER BY OrderName ASC

Output

 

 

 

The output of the above command will be data from column names OrderID, OrderName, OrderAddress, if and only if data is present in all mentioned columns, in ascending order and OrderName.

 

How to compare queries by using Exists and In statement 

 

The following example compares two queries that are semantically equivalent, i.e Programmatically they should result in the same output. The first query uses SQL Exists statement and the second query uses SQL In statement.

 

Syntax 

-- Uses sample
SELECT a.OrderName, a.orderAddress,a.OrderDate
FROM OrderDetails   AS a
WHERE EXISTS
(SELECT *
    FROM OrderDetails AS b
    WHERE a.OrderId = b.OrderId
    AND a.OrderName = 'Mango');
GO

Output

 

 

The output of the above command will be data from OrderName, OrderAddress, OrderDate if and only OrderName is 'Mango' and OrderId is the same.

 

Using Comparing queries by using Exists and = Any statement   

 

The following example shows two queries to find stores whose name is the same name as a vendor. The first query uses Exists and the second uses "=Any" statement.


 

SQL any operator returns if any of the subquery values meet the condition. 

 

Syntax 

-- Uses sample
SELECT DISTINCT s.OrderName
FROM OrderDetails  AS s
WHERE EXISTS
(SELECT *
    FROM EmployeeDetail  AS v
    WHERE s.OrderName = v.EmpName) ;
GO

The above query will distinguish OrderName from the OrderDetails table and select all data in the EmployeeDetail table and where Column name is orderName and EmpName in this statement, if sub-query returns anything, the subquery will return data from EmployeeDetails if OrderName is the same EmpName.  

 

The following query uses ="Any" 

 

Syntax

-- Uses sample
SELECT DISTINCT s.EmpName
FROM EmployeeDetail AS s
WHERE s.EmpName = ANY
(SELECT v.orderAddress
    FROM OrderDetails AS v ) ;
GO

The following query will return distinct values from EmpName,  and from EmployeeDetail table if the subquery returns data. The subquery will return data if the OrderDetails table has some not null values. 

 

How to Compare queries by using Exits and In statement 

 

The following example shows queries to find employees of departments that start with P. 

 

Syntax 

-- Uses sample
SELECT p.OrderName, p.orderAddress
FROM OrderDetails AS p
JOIN EmployeeDetail AS e
   ON e.EmpId = p.OrderId
WHERE EXISTS
(SELECT *
    FROM OrderDetails AS d
    JOIN Employee_Details AS edh
       ON d.OrderId = edh.OrderDate
    WHERE e.EmpId = edh.OrderName
    AND d.OrderName LIKE 'T%');
GO

The following query will return OrderAddress from the OrderDetails table and we are using Join to ease our task of getting identical data. You can read about join on Csharp Corner.

 

The subquery will return data if OrderName starts with "T" and EmpId and OrderName are the same. 

 

The following query uses In.

 

Syntax

-- Uses sample
SELECT p.OrderName, p.orderAddress
FROM OrderDetails AS p
JOIN EmployeeDetail AS e
   ON e.EmpId = p.OrderId
JOIN EmployeeDetail  AS edh
   ON e.EmpId = edh.EmpId
WHERE edh.EmpId IN
(SELECT OrderId, OrderName, orderAddress
   FROM OrderDetails
   WHERE OrderName  LIKE 'P%');  GO

The following query will return orderName, orderAddress from orderDetails table and we are using Join to case our task of getting identical data. The subquery will return OrderName, OrderAddress table if OrderName starts with"P" 

 

How to use Not Exists statement

 

The Not Exists statement works the opposite of Exists. The Where clause in Not Exists is satisfied if no rows are returned by the subquery.

 

The following example finds EmployeeDetail who are not in departments which have names that start with P.

 

Syntax 

-- Uses sample
SELECT p.OrderName, p.orderAddress
FROM OrderDetails AS p
JOIN EmployeeDetail AS e
   ON e.EmpId = p.OrderId
WHERE NOT EXISTS
(SELECT *
   FROM OrderDetails  AS d
   JOIN EmployeeDetail   AS edh
      ON d.OrderId = edh.EmpId
   WHERE e.EmpId = edh.EmpId
   AND d.OrderName LIKE 'P%')
ORDER BY EmpName,OrderName

The following query will return orderName, orderAddress from orderDetails table and we are using Join to case our task of getting identical data. The subquery will return OrderName, OrderAddress table if OrderName starts with P. 

 

How to use Exists statement 

 

The following example identifies whether any rows in the OrderName, OrderAddress  table could be matched to rows in the OrderDetails table.

 

The query will return rows only when both the OrderName and EmpId values in the two tables match. 

 

Syntax 

-- Uses sample
SELECT a.OrderName, a.orderAddress
FROM OrderDetails  AS a
WHERE EXISTS
(SELECT *
    FROM EmployeeDetail  AS b
    WHERE (a.OrderName = b.EmpId) AND (a.orderAddress = b.EmpAddress));

In the above code, we will get the rows from EmployeeDetail table, if and only if OrderName is equal to EmpIF and orderAddress is the same as the EmpAddress.

 

How to use Not Exists statement

 

Not Exists works the opposite of Exits. The Where clause in Not Exists is satisfied if no rows are returned by the subquery.

 

The following example finds rows in the OrderDetails table where the OrderName and EmpId do not match any entries in the EmployeeDetail table. 

 

Syntax

-- Uses sample
SELECT a.OrderName, a.orderAddress
FROM OrderDetails  AS a
WHERE NOT EXISTS
(SELECT *
    FROM EmployeeDetail  AS b
    WHERE (a.OrderName = b.EmpId) AND (a.orderAddress = b.EmpAddress));

The above query will return OrderName, OrderAddress from OrderDetails if the subquery does not result in any output.

 

The above example identifies whether any rows in the OrderName, OrderAddress table could be matched to rows in the EmployeeDetail table.

 

Summary  

In this article, you learned how to use a SQL Select Exists statement with various options.