SQL Help in Access – Looking for the Absence of Data

Question

I am trying to find the fastest way to find all of the records in a parent table that do not have certain records in a child table. For example, I want the query to return all of the family records where there are no male children or no children at all.

Example 1

This is painfully slow:

SELECT * FROM Families
WHERE Families.FamilyID NOT IN(SELECT FamilyID FROM Children WHERE Children.Gender="m")

Example 2

This is faster:

SELECT * FROM Families
WHERE NOT EXISTS(SELECT * FROM Children WHERE Children.Gender="m" AND Children.FamilyID = Families.FamilyID)

Example 3

This is the fastest, but I'm worried about investing in it because I can't find any documentation on it. I don't even know what it's called. For want of a better term, we have been calling it an "anonymous query".

SELECT * FROM Families
LEFT JOIN
[SELECT * FROM Children WHERE Gender="m"]. AS AliasChildren ON Families.FamilyID=AliasChildren.FamilyID
WHERE AliasChildren.FamilyID IS NULL

So my question is this:

1. What is this thing called in example 3?
2. Is it a "mainstream" feature of Access that will be supported in the future?
3. Where can I find documentation on it (this might be easier once I know what it's called)?

Thanks!

Answer 1

Example 3 is termed a derived table, but you can simplify the query to this:

SELECT * FROM Families
LEFT JOIN
Children ON Families.FamilyID = Children.FamilyID AND Children.Gender="m"
WHERE Children.FamilyID IS NULL