如何查询Memo为Local Object且ID未在Parent列出现的记录ID

Great question! Your existing NOT EXISTS query is actually solid and efficient in most databases, but there are a few alternative approaches you can consider depending on your database system and performance needs. Let's break them down:

1. LEFT JOIN + IS NULL Approach #

This is a widely used alternative that's often optimized similarly to NOT EXISTS in modern databases:

SELECT t1.ID
FROM db.myTable t1
LEFT JOIN db.myTable t2 ON t2.Parent = t1.ID
WHERE t1.Memo = 'local object'
  AND t2.ID IS NULL;

How it works: We join the table to itself where a row's Parent matches another row's ID. Then we filter out any rows where the join found a match (indicated by t2.ID IS NULL), leaving only local objects that never appear as a parent.

2. EXCEPT Clause (For Supported Databases) #

If your database (like PostgreSQL, SQL Server, or BigQuery) supports the EXCEPT operator, this is a clean, readable option:

SELECT ID
FROM db.myTable
WHERE Memo = 'local object'
EXCEPT
SELECT Parent
FROM db.myTable
WHERE Parent IS NOT NULL;

This logic is straightforward: Grab all IDs that are 'local object's, then subtract any IDs that appear in the Parent column. The WHERE Parent IS NOT NULL ensures we don't accidentally exclude valid results due to null values in the Parent column.

3. NOT IN (Use With Caution) #

You can use NOT IN, but be wary of null values in the Parent column—if there's any null present, NOT IN will return no results. If you're certain Parent has no nulls, or you add a filter to exclude them, this works:

SELECT ID
FROM db.myTable
WHERE Memo = 'local object'
  AND ID NOT IN (SELECT Parent FROM db.myTable WHERE Parent IS NOT NULL);

Performance Tips #

No matter which query you choose, indexing will drastically improve speed:

• Create an index on (Memo, ID) to quickly locate all 'local object' rows.
• Create an index on Parent to speed up checking if an ID ever appears as a parent.

Always compare execution plans in your specific database to see which approach is fastest—modern query optimizers often treat NOT EXISTS and LEFT JOIN IS NULL identically, but results can vary by system.

内容的提问来源于stack exchange，提问作者Red'n'Black