SQL Exists Statement

Introduction

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

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 
  1. 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 
  1. --Uses sample     
  2. SELECT OrderID,OrderName, orderAddress From OrderDetail     
  3. Where EXISTS (SELECT NULL)    
  4. 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 a 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 a statement.
 
Syntax 
  1. -- Uses sample            
  2. SELECT a.OrderName, a.orderAddress,a.OrderDate        
  3. FROM OrderDetails   AS a        
  4. WHERE EXISTS        
  5. (SELECT *         
  6.     FROM OrderDetails AS b        
  7.     WHERE a.OrderId = b.OrderId        
  8.     AND a.OrderName = 'Mango');        
  9. 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 
  1. -- Uses sample           
  2. SELECT DISTINCT s.OrderName        
  3. FROM OrderDetails  AS s         
  4. WHERE EXISTS        
  5. (SELECT *        
  6.     FROM EmployeeDetail  AS v        
  7.     WHERE s.OrderName = v.EmpName) ;        
  8. GO     
The above query will distinguish OrderName from the OrderDetails table and select all data in the EmployeeDetail table and where the 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
  1. -- Uses sample           
  2. SELECT DISTINCT s.EmpName       
  3. FROM EmployeeDetail AS s         
  4. WHERE s.EmpName = ANY        
  5. (SELECT v.orderAddress        
  6.     FROM OrderDetails AS v ) ;        
  7. GO        
The following query will return distinct values from EmpName,  and from the 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 a statement? 

 
The following example shows queries to find employees of departments that start with P. 
 
Syntax 
  1. -- Uses sample          
  2. SELECT p.OrderName, p.orderAddress        
  3. FROM OrderDetails AS p         
  4. JOIN EmployeeDetail AS e        
  5.    ON e.EmpId = p.OrderId         
  6. WHERE EXISTS        
  7. (SELECT *        
  8.     FROM OrderDetails AS d        
  9.     JOIN Employee_Details AS edh        
  10.        ON d.OrderId = edh.OrderDate        
  11.     WHERE e.EmpId = edh.OrderName        
  12.     AND d.OrderName LIKE 'T%');        
  13. 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
  1. -- Uses sample          
  2. SELECT p.OrderName, p.orderAddress        
  3. FROM OrderDetails AS p         
  4. JOIN EmployeeDetail AS e        
  5.    ON e.EmpId = p.OrderId         
  6. JOIN EmployeeDetail  AS edh        
  7.    ON e.EmpId = edh.EmpId         
  8. WHERE edh.EmpId IN        
  9. (SELECT OrderId, OrderName, orderAddress         
  10.    FROM OrderDetails        
  11.    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 
  1. -- Uses sample          
  2. SELECT p.OrderName, p.orderAddress        
  3. FROM OrderDetails AS p         
  4. JOIN EmployeeDetail AS e        
  5.    ON e.EmpId = p.OrderId         
  6. WHERE NOT EXISTS        
  7. (SELECT *        
  8.    FROM OrderDetails  AS d        
  9.    JOIN EmployeeDetail   AS edh        
  10.       ON d.OrderId = edh.EmpId        
  11.    WHERE e.EmpId = edh.EmpId        
  12.    AND d.OrderName LIKE 'P%')        
  13. ORDER BY EmpName,OrderName      
The above 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. 
 
The following example finds rows in the OrderDetails table where the OrderName and EmpId does not match any entries in the EmployeeDetail table. 
 
Syntax
  1. -- Uses sample         
  2. SELECT a.OrderName, a.orderAddress        
  3. FROM OrderDetails  AS a        
  4. WHERE NOT EXISTS        
  5. (SELECT *         
  6.     FROM EmployeeDetail  AS b        
  7.     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.
 

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 
  1. -- Uses sample         
  2. SELECT a.OrderName, a.orderAddress        
  3. FROM OrderDetails  AS a        
  4. WHERE EXISTS        
  5. (SELECT *         
  6.     FROM EmployeeDetail  AS b        
  7.     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.
 

Summary 

 
In the next chapter, we will learn how to use a SQL INDEX statement with various options.
Author
Naresh Beniwal
Editor 7.1k 1.5m
Next » SQL Index Statement

©2024 C# Corner.