How can I explain NOT EXISTS in layman terms

Question

And to myself for that matter...

I just hate the negative connotation and having to try and explain NOT EXISTS.

Can someone give me a good way of explaining NOT EXISTS to the business community?

Answer 1

And if you are explaining this to non-technical people, don't forget to use examples that are meaningful to your audience. So, for example, your company might want to contact all customers who have placed an order in the last 6 months, but have not ordered in the last quarter to send them a coupon or a "We want you back" letter. That query would look like

Select c.AccountNumber 
From Customer c
Where  
  -- Customers who have ordered in the last 180 days
  Exists (Select * From SalesOrderHeader s1 Where c.CustomerID = s1.CustomerID And DateDiff(day, s1.OrderDate, GetDate()) < 180)
  -- But have not ordered in the last 90 days
  And Not Exists (Select * From SalesOrderHeader s2 Where c.CustomerID = s2.CustomerID And DateDiff(day, s2.OrderDate, GetDate()) < 90)
Group By c.AccountNumber;

You could use that to explain both Exists and Not Exists.

Tom

Answer 2

Start with EXISTS, that is likely to be easier. For a query like:

SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.col = B.col)

I explain this is evaluate the subquery in the EXISTS for every row in a, and if the subquery returns at least one row, the row from A should appear in the output.

With NOT EXISTS, it's the other way round, if the subquery comes back empty the row from A should be displayed.

Answer 3

Hi @Bobby P ,

Thank you so much for posting here.

If I need to explain NOT EXISTS to the ones who have limited knowledge about SQL Server, I would like to use images rather than words.

You could refer below snapshot of both scenarios about EXISTS and NOT EXISTS and check whether it is a little helpful to you.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet