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

What is this thing called in example 3?
It is called a "Derrived table"

Is it a "mainstream" feature of Access that will be supported in the future?
It is a standard feature of ANSI SQL as far as I know and is very commonly used.

Where can I find documentation on it (this might be easier once I know what it's called)? Here is a blog article about them

In any case the query posed by Mitch is your best bet:

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

Answer 2

I think you could also try something like this:

SELECT * FROM Families WHERE Families.FamilyID IN ( SELECT Children.FamilyID FROM Children WHERE Children.Gender<>'m' GROUP BY Children.FamilyID )

Answer 3

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