技术问询：如何查询未在assemble表与用户关联的product表记录

Alright, let's break down what you need here. You want to fetch all products where no row in the assemble table pairs that product's pid with any uid from the user table. In other words, either the product's pid never appears in assemble at all, or when it does appear, all associated uids aren't present in the user table.

Here are a few reliable approaches to write this query:

1. Use NOT EXISTS (Most Intuitive & Safe) #

This is my go-to method for these kinds of exclusion queries—it's straightforward and avoids common pitfalls with null values.

SELECT p.pid, p.pname
FROM product p
WHERE NOT EXISTS (
    -- Check if there's any assemble record linking this product to a valid user
    SELECT 1
    FROM assemble a
    JOIN user u ON a.uid = u.uid
    WHERE a.pid = p.pid
);

How it works: #

The subquery checks if there's any row in assemble that matches the current product's pid and is associated with a real user (via the join to user). If no such row exists, the product is included in the result set.

2. Use LEFT JOIN + IS NULL #

Another common pattern is to use left joins to find unmatched records:

SELECT DISTINCT p.pid, p.pname
FROM product p
LEFT JOIN assemble a ON p.pid = a.pid
LEFT JOIN user u ON a.uid = u.uid
WHERE u.uid IS NULL;

How it works: #

We left-join product to assemble, then left-join that result to user. If a product has no matching assemble records, or all its assemble records link to uids not present in user, the u.uid column will be NULL. We use DISTINCT to avoid duplicate product rows if a product has multiple invalid assemble entries.

3. Use NOT IN (Use with Caution) #

This works only if you're certain there are no NULL values in user.uid or assemble.uid—nulls can break NOT IN logic unexpectedly.

SELECT p.pid, p.pname
FROM product p
WHERE p.pid NOT IN (
    -- Get all pids that have been paired with a valid user in assemble
    SELECT a.pid
    FROM assemble a
    WHERE a.uid IN (SELECT u.uid FROM user u)
);

How it works: #

First, we get all pids that have been linked to a user in assemble, then select products whose pids aren't in that list. Again, avoid this if there's any chance of nulls in the uid columns.

Pro Tip: #

For large datasets, make sure you have indexes on assemble.pid, assemble.uid, and user.uid—this will drastically speed up your query execution.

内容的提问来源于stack exchange，提问作者Akshay Vyas