SQL - SQL Not Exists: Filter Out Records that Exist in a Subquery

SQL Not Exists: Filter Out Records that Exist in a Subquery

Subqueries and SQL Statements

A subquery is basically a query within a query. The following query is a standard, simple SQL select statement:

select * from customers where customerId = 5

The “select *” part tells the SQL database to return all columns. The asterisk in SQL indicates that you want to return all columns, but you can also specify columns individually.

The “where” clause indicates that you want to return just customers with an id of 5. You can, of course, return more than one record. You can assume from the above statement that “customerId” is the unique column. A customerId should only be one value per customer, so you can assume the above query only returns one record.

Instead of using the “where customerId = 5” clause, you can add a subquery. That’s where NOT EXISTS works. For instance, take the following query:

select * from customers where NOT EXISTS (select customerId from orders)

The above statement basically says “give me all customer records where the customerId does not exist in the orders table.” Therefore, the business logic is “I want to see all customers who do not have orders.” The NOT EXISTS is the opposite of the EXISTS statement, so you just reverse the business logic if you want to see the opposite results. For instance, the following statement gives you the opposite results:

select * from customers where EXISTS (select customerId from orders)

The above statement says “give me all customer records where the customer has placed an order.” You’ll probably need to use EXISTS and NOT EXISTS in different parts of your application depending on your business logic.

When you run a query such as the ones above, it should be noted that the subquery runs first. The subquery in the EXISTS and NOT EXISTS statements is the query that returns order records. This query runs first. Then, the main or “outer” query runs. In the above examples, the outer query is the select statement based on the customers table. Consider the following query:

select * from customers where NOT EXISTS (select customerId from orders where createdate >= getdate() - 30)

In this statement, a list of orders is returned based on the order date. It’s assumed that “createdate” is the column that contains the order’s creation date. Therefore, any order placed within the last 30 days is returned from the subquery. These records are then used for the outer query, which is again the select statement run against the customers table.

Combining NOT EXISTS with Other Where Clause Filters

The where clause can take multiple logic filters. Suppose you want to get a list of customers who placed an order, but you only want customers who have signed up within the last 60 days. For instance, you might want to know how many of your customers placed orders, but you want “new” customers. “New” is an arbitrary value, so you set that value at 60 days. You can write SQL statements that match this business logic. You can usually write SQL statements in several ways to match the business logic, but in this example you want to use the NOT EXISTS statement. The following code is a SQL statement that codes for the specified business logic:

select * from customers where EXISTS (select customerId from orders where createdate >= getdate() - 30) and NOT EXISTS (select customerid from customers where createdate <= getdate() – 60)

The above statement combines the EXISTS and NOT EXISTS statements.

First, the EXISTS subquery runs. This is a subquery that gets a list of customers where orders were placed within the last 30 days.

Next, the NOT EXISTS subquery runs. This subquery gets a list of customers that were created prior to 60 days ago.

Since the second subquery uses the NOT EXISTS statement, the main query does a match with the NOT EXISTS subquery against the customer database, and filters out records where they exist in the subquery. Therefore, it only gets records where the customerId is not located in the NOT EXISTS subquery.

 

Insight

If subquery without any matching key with the main query like following, no rows will be return; For example, if there is not price column in the customers table, the following statement will not procure any result.

select * from customers where EXISTS (select price from pricelist)

 

Example

SELECT ColA, ColB, Col1, Col2, Col3, Col4 

FROM TableA 

WHERE (NOT EXISTS

 (SELECT ColA, ColB, Col5, Col6, Col7, Col8

  FROM TableB

  --WHERE (TableA.ColA = ColA) AND 

  --      (TableA.ColB = ColB)   

  ))

ColA and ColB must be Keys.

SELECT device, purchdoc, processhr, receivedt, invdate, 

                 invno, pay_no_pay, potp, flag, uploadeddt, 

FROM TableA

WHERE  ( NOT EXISTS

 (SELECT timelog, assignedlotno, remainingqty, do_status, todo, sumgoodqty, 

                   sumrejqty, bihr, devicename, invoiceno, datecode, matcheddate, item, 

  sourcedevice, purchdoc, netprice, processhr, receivedt, invdate, invno, pay_no_pay,                          devnumber

  FROM TableB

  --WHERE  (TableA.processhr      = processhr) AND 

  --                 (TableA.sourcedevice = devnumber) AND 

  --                 (TableA.purchdoc       = purchdoc)

  ))