USING EXISTS STATEMENT IN MSSQL

In Microsoft SQL Server (MSSQL), the EXISTS statement is used to test whether a subquery returns any rows.

The EXISTS statement in SQL Server is commonly used in various scenarios. Here are some use cases where EXISTS is frequently employed:

Use case 1: Checking for the Existence of Records:

Use EXISTS to check whether there are any records that satisfy a certain condition.

Syntax:-

   IF EXISTS (SELECT 1 FROM TableName WHERE SomeCondition)
   BEGIN
       -- Do something if records exist
   END

Example:-

As an example, from AdventureWorks2019 database, in Sales table,

IF EXISTS (SELECT 1 FROM Sales.SalesOrderDetail WHERE LineTotal > 10000)
   BEGIN
      Print 'Sales line total exceeds 10000'
   END
   ELSE
   BEGIN
    PRINT 'Sales line total does not exceed 10000';
   END

Result:-

Executing the query will give the following result.

Usecase 2: Correlated Subqueries:

Utilize EXISTS in correlated subqueries to check for the existence of related records.

Syntax:

   SELECT
       Column1,
       Column2
   FROM
       Table1 AS T1
   WHERE
       EXISTS (SELECT 1 FROM Table2 AS T2 WHERE T2.ForeignKey = T1.PrimaryKey);

Example:

The following SQL lists all products where exists a sales order which has a line total greater than 10k

--List of products where exists a sales order which has a line total greater than 10k
      SELECT 
        p.Name AS ProductName
       ,p.ProductNumber AS ProductNumber
	   ,p.StandardCost AS StandardCost
    FROM Production.Product p 
   WHERE
       EXISTS (SELECT 1 FROM Sales.SalesOrderDetail sod  WHERE sod.ProductID = p.ProductID and     sod.LineTotal > 10000);

Result:

Executing the query will result in

Usecase 3: Conditional Inserts or Updates:

Use EXISTS to conditionally insert or update records based on whether certain conditions are met.

Syntax:

   INSERT INTO TargetTable (Column1, Column2)
   SELECT Value1, Value2
   WHERE EXISTS (SELECT 1 FROM TargetTable WHERE SomeCondition);

Example:

Let’s create a dummy table and try to insert into this table only when a certain condition exists.

CREATE TABLE SpecialOffers_Dummy 
(
  Product_ID INT
, ProductName VARCHAR(255)
)

INSERT INTO SpecialOffers_Dummy (Product_ID
				,ProductName)
            SELECT p.ProductID
		  ,p.Name AS ProductName
	    FROM   Production.Product p 
            WHERE  EXISTS (SELECT 1 FROM Sales.SalesOrderDetail sod  WHERE sod.ProductID = p.ProductID    and sod.LineTotal > 25000);

SELECT * FROM SpecialOffers_Dummy;

Result:

The above query inserts Product data into the SpecialOffers_Dummy table when we have Line Total > 25000

Usecase 4: Deleting Records Conditionally:

Use EXISTS to conditionally delete records based on certain criteria.

Syntax:

   DELETE FROM TargetTable
   WHERE EXISTS (SELECT 1 FROM OtherTable WHERE OtherTable.ForeignKey = TargetTable.PrimaryKey);

Example:

We can use the same dummy table created for the previous example.

Let’s say we want to delete the products which are in Silver color.

So we need to see if we have any products that exists in the Products table which are in silver color and that matches with our list of products in SpecialOffers_dummy table and delete them.

The following code is used to do the same.

DELETE FROM SpecialOffers_Dummy 
   WHERE EXISTS (SELECT 1 FROM Production.Product p WHERE Product_ID = p.ProductID and p.Color = 'Silver');

Result:

The result of the query deletes the Product which is in silver color

Usecase 5: Using NOT EXISTS:

Similar to EXISTS, we can also use NOT EXISTS to check for the absence of records that meet certain conditions.

Syntax:

   SELECT
       Column1,
       Column2
   FROM
       TableName
   WHERE
       NOT EXISTS (SELECT 1 FROM OtherTable WHERE SomeCondition);

Example:

We can take all the 4 examples listed above and do the same with NOT EXISTS.

  SELECT 
        p.Name AS ProductName
       ,p.ProductNumber AS ProductNumber
	   ,p.StandardCost AS StandardCost
    FROM Production.Product p 
   WHERE
       NOT EXISTS (SELECT 1 FROM Sales.SalesOrderDetail sod  WHERE sod.ProductID = p.ProductID and sod.LineTotal > 10000);

Result:

Conclusion:-

• These are just a few use cases, and the EXISTS statement can be applied in various other scenarios depending on the specific requirements of your queries and data manipulation tasks in SQL Server.

• In real time query writingEXISTS and NOT EXISTS becomes very handy as most of the time, we need to check for certain conditions before performing any operations in the data.