Alternative methods 2

1. Alternative methods 2

Another way to determine if data in one table is present, or absent, in a related table, is with joins. In this lesson, we’ll discuss the INNER JOIN and the exclusive LEFT OUTER JOIN. We’ll finish off with a review of the different methods presented in this chapter and the advantages and disadvantages of each one.

2. INNER JOIN

When joining queries, with an INNER JOIN, the left query joins the right query through related columns. An INNER JOIN will match values in the related columns and return the results of the match. Therefore, this is a check for the presence of data in related tables. In this example, the Customers and Orders tables both contain the column CustomerID. This is the column which relates these tables.

3. LEFT OUTER JOIN

To check for the presence of data in one table that is absent in another table with a JOIN, we can use an exclusive LEFT OUTER JOIN. An inclusive LEFT OUTER JOIN returns all the rows in the left query whereas an exclusive LEFT OUTER JOIN returns only rows in the left query that are not present in the right query. We can demonstrate this concept with Venn diagrams. The highlighted areas represent the results of the join between queries.

4. Exclusive LEFT OUTER JOIN

To make an exclusive LEFT OUTER JOIN we add IS NULL to a WHERE filter condition of the right query to restrict it to rows that do not match. This example returns Customer data from the Customers table that is absent from the Orders table.

5. Review: INTERSECT and EXCEPT

To end this chapter let’s review the different methods to determine if data in one table is present, or absent, in a related table and the advantages and disadvantages of each one. First, INTERSECT and EXCEPT. INTERSECT checks for the presence and EXCEPT for the absence. The advantages are that they are great for data interrogation and they remove duplicates from the returned results. A disadvantage is that the number and order of columns in the SELECT statement must be the same between queries.

6. Review: EXISTS and NOT EXISTS

Next: EXISTS and NOT EXISTS. Both of these operators use a sub-query in the WHERE filter condition. EXISTS checks for the presence and NOT EXISTS for the absence. One advantage is that the sub-query with stop searching as soon as it evaluates to TRUE, which is a match for EXISTS, and non-match for NOT EXISTS. Another advantage is the results can contain any column from the outer query, and in any order, which is an advantage over INTERSECT and EXCEPT. Restricting results to columns from the outer query only is also a disadvantage, compared to a JOIN.

7. Review: IN and NOT IN

Next: IN and NOT IN. IN checks for the presence and NOT IN for the absence. An advantage is the results can contain any column from the outer query. Like EXISTS and NOT EXISTS this is an advantage over INTERSECT and EXCEPT. Like EXISTS and NOT EXISTS, restricting results to columns from the outer query only is a disadvantage compared to a join. Another disadvantage is no results returned with NOT IN if the column being evaluated in the sub-query contains nulls.

8. Review: INNER JOIN and exclusive L.O.J

And finally, the INNER JOIN and the exclusive LEFT OUTER JOIN. INNER JOIN checks for the presence and exclusive LEFT OUTER JOIN for the absence. One advantage of joins is that the results can contain any column, from all joined queries, in any order. Compare this to EXISTS, NOT EXISTS, IN, and NOT IN where the results are restricted to columns from the outer query only. A possible disadvantage of the exclusive LEFT OUTER JOIN is the requirement to add the IS NULL WHERE filter condition. In a large complex query, this could be easily missed.

9. Let's practice!

Let's practice

Create Your Free Account

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