Alternative methods 1

1. Alternative methods 1

In this chapter, we’ve already looked at how we can use INTERSECT with EXCEPT to determine if data in one table is present, or absent, in a related table. In this lesson we’ll examine alternative methods: EXISTS, NOT EXISTS, IN and NOT IN.

2. EXISTS

Let's start with EXISTS. EXISTS filters the outer query when there is a match of data between the outer query and a sub-query. EXISTS evaluates for either TRUE or FALSE on a match, therefore we can specify anything in the SELECT statement of a sub-query. In this example, if CustomerID from the Customers table matches CustomerID from the Orders table, a one is returned indicating it is TRUE and the Customers table is filtered on the match.

3. IN

Next IN. IN works in a similar way with sub-queries. However, we need to specify the columns, to match on, in the WHERE filter condition of the outer query, and the SELECT statement of the sub-query. Here we are looking for a match of CustomerID from the Customers table with CustomerID from the sub-query, which in this case is just the Orders table.

4. EXISTS vs. IN

In our two examples of EXISTS and IN, the results are identical. Is one method better than the other? It depends. The sub-query used with EXISTS looks like a correlated sub-query which we said previously can be inefficient. Technically yes, it is a correlated sub-query. However, EXISTS will stop searching the sub-query when the condition is TRUE whereas IN, with a sub-query, will collect all the results from the sub-query before further processing, which is potentially slower. For this reason, consider using EXISTS instead of IN with a sub-query.

5. NOT EXISTS

NOT EXISTS and NOT IN are used to do the opposite of EXISTS and IN. In a query, substitute EXISTS and IN with NOT EXISTS and NOT IN respectively to check for the presence of data in one table that is absent in another. This example uses NOT EXISTS to return Customer details from the Customers table where the Customer is absent from the Orders table.

6. NOT IN

And this example uses NOT IN to perform the same check. Here we need to specify the column we want to filter on in the WHERE filter condition.

7. NOT IN and NULLs

One major issue with using NOT IN is the way it handles NULL values. If the columns in the sub-query, being evaluated for a non-match, contain NULL values, no results are returned. In this example, we want to know which Nation capitals, from the Nations table, are absent from the NearestPop column in the Earthquakes table — using NOT IN returns no results. Does that mean every capital city has been near to a recorded earthquake?

8. Handling NOT IN NULLs

No. It is because NearestPop column contains NULL values. To handle NULL values, the sub-query needs an IS NOT NULL filter. Here, we filter the sub-query to remove NULL values. Because of this issue, we should always consider using NOT EXISTS instead of NOT IN with a sub-query.

9. EXISTS, NOT EXISTS, IN and NOT IN

An advantage of EXISTS, NOT EXISTS, IN and NOT IN over other methods is the results can contain any column from the outer query, and in any order. Compare this to INTERSECT and EXCEPT where the number and order of columns in the SELECT statement must be the same between queries. As previously discussed, one disadvantage of NOT IN is the way it handles NULL values in the sub-query.

10. Let's practice!

Let's practice these alternative methods.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.