4.7. EXISTS Conditions

An EXISTS condition is very similar to an IN condition with a subquery. The difference is that the EXISTS condition’s subquery merely needs to return any rows, be it a million or just one, in order for the EXISTS condition to evaluate to TRUE. Furthermore, it does not matter what columns make up those rows—merely that some rows exist (hence the name).

To demonstrate the use of EXISTS, we’ll use the Shopping Cart sample application again, but this time focus on the customers and their carts. To put these terms in context here, a customer is a person who has registered on the web site, and a cart is the collection of items that the customer has selected for purchase. Let’s say we want to find all the customers who have yet to create a cart. The key idea here is the not part of the requirement, so we’ll use NOT EXISTS in the solution:

listing 4-9. Cart_07_NOT_EXISTS_and_NOT_IN.sql (excerpt)

SELECT
  name
FROM
  customers
WHERE
  NOT EXISTS (
    SELECT
      1 
    FROM
      carts
    WHERE
      carts.customer_id = customers.id
  )

As you can see, we're using a correlated subquery again within the WHERE clause. This time, the correlation variable is not a table alias, but rather just the name of the table in the outer query. In other words, the subquery will return rows from the carts table where the cart’s customer_id column is the same as the id column in the customers table in the outer or main query. If a customer has one or more carts, as returned by the subquery, EXISTS would evaluate to TRUE. However, we're using NOT EXISTS in the main query so a customer's name will only be included in the result set if there are no carts for the customer returned by the subquery, exactly as required.

But what, you may well ask, is SELECT 1 all about? Well, as noted earlier, the EXISTS condition does not care which columns are selected, so SELECT 1 simply returns a column containing the numeric constant 1. The subquery could just as easily have selected the customer_id column. EXISTS will evaluate TRUE or FALSE, no matter which columns the subquery selects. We’ll cover the SELECT clause in detail in Chapter 7.

4.7.1. NOT IN or NOT EXISTS?

The query above can be rewritten using a NOT IN condition rather than a NOT EXISTS condition, if required. In fact, it can be written in two different ways using NOT IN. The first way is to use an uncorrelated subquery:

listing 4-10. Cart_07_NOT_EXISTS_and_NOT_IN.sql (excerpt)

SELECT
  name
FROM
  customers
WHERE
  NOT (
    id IN (
      SELECT
        customer_id
      FROM
        carts
    )
  )

The second way uses a correlated subquery:

listing 4-11. Cart_07_NOT_EXISTS_and_NOT_IN.sql (excerpt)

SELECT
  name
FROM
  customers AS t
WHERE
  NOT (
    id IN (
      SELECT
        customer_id
      FROM
        carts 
      WHERE 
        customer_id = t.customer_id
    )
  )

Which is better? That’s the subject of the next section: performance.

4.7.1.1. A Left Outer Join with an IS NULL Test

Incidentally, the same query can also be rewritten as a LEFT OUTER JOIN with a test for an unmatched row. We saw in the previous chapter that a left outer join will return NULLs in the columns of the right table for unmatched rows. In this case, we want customers without a cart, and the query is:

listing 4-12. Cart_08_LEFT_OUTER_JOIN_with_IS_NULL.sql (excerpt)

SELECT
  customers.name
FROM
  customers
    LEFT OUTER JOIN carts
      ON customers.id = carts.customer_id
WHERE 
  carts.customer_id IS NULL

Because it’s a left outer join, this query returns rows from the left table—in this case, customers—with matching rows, if any, from the right table. If there are no matching rows, then the columns in the result set which would have contained values from the right table are set to NULL. So then, if we test for NULL in the right table’s join column, this will allow the WHERE clause to filter out all the matched rows, leaving only the unmatched rows. In other words, testing for NULL effectively returns customers without a cart.

Note that the correct syntax to test for NULL is: IS NULL. You cannot use the equals operator (WHERE carts.customer_id = NULL), because NULL is not equal to anything.