"Find Unmatched" Queries and How They Work

Consider the following scenario. You're a librarian, and you're building a database to keep track of your books. Because a book may have more than one author, and an author may have written more than one book, you decide to use three tables to store the basic information about books and authors, as in the following greatly simplified layout:

Table: Books
	Field: BookID (pk)
	Field: Title
	(other fields ...)	

Table: Authors
    Field: AuthorID (pk)
    Field: AuthorName
    (other fields ...)

Table: BooksAuthors
    Field: BookID (fk Books, compound pk)
    Field: AuthorID (fk Authors, compound pk)

The tables are related as follows:

Books 1<-->M BooksAuthors 1<-->M Authors

All very simple and clean, no? On the Books form you can have a subform based on BooksAuthors to display the author(s) of each book, and on the Authors form you can have a subform based on BooksAuthors to display the books written by that author.

But you want to make sure that you have recorded the author, or authors, for every book. So you want to build a query that will return all the books that don't have any matching records in the BooksAuthors table. The "Find Unmatched Query Wizard" will build such a query for you, but suppose you wanted to do it yourself. How would you go about it?

It's not that complicated when you think about it the right way. You want the query to return records from Books, right? So to start with, you know you need

	SELECT Books.*
	FROM Books

Of course, you may not need all fields from that table, so you could specify individual fields if you wanted, but let's keep it simple and ask for all of them.

Now, you know you need to refer to BooksAuthors, and you know that table is related to Books by the common key field, BookID. So you'll have to join the two tables in the query on that field:

SELECT Books.*
    FROM Books
    <join-type> JOIN BooksAuthors
    ON Books.BookID=BooksAuthors.BookID

But what type of join? Well, we're looking for Books records that don't have matching BooksAuthors records, so we can't use an inner join. We need a join that will return all records from Books, regardless of whether there's a match or not. Since Books is on the left side of the JOIN keyword in the query as we've built it, that would be a left join:

    SELECT Books.*
    FROM Books
    LEFT JOIN BooksAuthors
    ON Books.BookID=BooksAuthors.BookID

Now we need to apply criteria that screen out any records that actually found a match. How will we recognize them? Let's see ... in the joined records, if there was no match in BooksAuthors, all the fields from BooksAuthors will be Null. Now, some of those fields might be Null even in a matched record, but you know for sure that the BookID field couldn't be one of them, because that's the field you're joining on. Any record in the joined results that has a Null in BooksAuthors.BookID *must* be an unmatched record from Books. Conversely, any record in the joined results that doesn't have a Null in BooksAuthors.BookID must be a matched record. So if we take the result set of the join and filter out all records that don't have Null in BooksAuthors.BookID, we'll be left with the Books records that don't have matches in BooksAuthors:

    SELECT Books.*
    FROM Books
    LEFT JOIN BooksAuthors
    ON Books.BookID=BooksAuthors.BookID
    WHERE BooksAuthors.BookID Is Null;

And there's your query.

all that follows is amplification

I think the trick to understanding joins is to realize that the result set of the join itself -- before you select specific fields from it -- always contains all the fields from both sides of the join. But if it's an outer join, and one side has no matching records, the fields on that side are all Null. That's really what the query designer means when it says, "Include ALL records from 'Books' and only those records from 'BooksAuthors' where the joined fields are equal". If you're going to include all the records from one side, and only the matching records from the other side, what will go in the fields that should have come from the other side, for unmatched records? The answer is, Nulls.

Let's follow it through with a simple example. Suppose you have these records in table Books:

    BookID  Title
    ------  ----------------------------
    1       The Lord of the Rings
    2       A Wizard of Earthsea
    3       Damiano

and these records in table Authors:

    AuthorID  AuthorName
    --------  --------------------------
    1         R. A. McAvoy
    2         Ursula K. Le Guin
    3         J. R. R. Tolkien

and these records in table BooksAuthors:

    BookID  AuthorID
    ------  --------
    1       3
    3       1

Then the join

    Books As B LEFT JOIN BooksAuthors As BA
    ON Books.BookID=BooksAuthors.BookID

will return this set of records before any criteria are applied:

	B.BookID  B.Title                BA.BookID  BA.AuthorID
--------  ---------------------  ---------  --------------
1         The Lord of the Rings  1          3
2         A Wizard of Earthsea   Null       Null
3         Damiano                3          1

It's pretty obvious that the book with no author recorded is the one for which BA.BookID is Null, _A Wizard of Earthsea_.