如何在SQLite中查询未订购对应商品的用户与商品组合？

Absolutely! You can easily implement this query in SQLite. The core idea is to first generate all possible user-item combinations (the full cartesian product of users and items), then exclude the pairs that already exist in the item_user association table. Here's how to do it step by step:

1. Setup Test Data (for Verification) #

First, let's create the tables and insert your sample data so you can test the query directly:

-- Create tables
CREATE TABLE item (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE item_user (
    item_id INTEGER, 
    user_id INTEGER, 
    FOREIGN KEY(item_id) REFERENCES item(id), 
    FOREIGN KEY(user_id) REFERENCES user(id)
);

-- Insert sample data
INSERT INTO item VALUES (1, 'hammer'), (2, 'nail');
INSERT INTO user VALUES (1, 'Bob'), (2, 'Jane'), (3, 'Danny');
INSERT INTO item_user VALUES 
    (1, 1), -- Bob ordered hammer
    (2, 1), -- Bob ordered nail
    (1, 2), -- Jane ordered hammer
    (2, 2), -- Jane ordered nail
    (1, 3); -- Danny ordered hammer

2. Query to Find Unordered Pairs #

There are two common, efficient ways to write this query:

Method 1: Using CROSS JOIN + LEFT JOIN #

This approach generates all possible pairs, then uses a left join to identify pairs with no matching entry in item_user:

SELECT 
    u.name AS "user.name", 
    i.name AS "item.name"
FROM user u
CROSS JOIN item i
LEFT JOIN item_user iu 
    ON u.id = iu.user_id 
    AND i.id = iu.item_id
WHERE iu.item_id IS NULL;

Method 2: Using CROSS JOIN + NOT EXISTS #

This method checks for each user-item pair whether there's no corresponding entry in item_user (often performs well with proper indexing):

SELECT 
    u.name AS "user.name", 
    i.name AS "item.name"
FROM user u
CROSS JOIN item i
WHERE NOT EXISTS (
    SELECT 1
    FROM item_user iu
    WHERE iu.user_id = u.id 
      AND iu.item_id = i.id
);

3. Expected Result #

Both queries will return exactly the output you're looking for:

user.name   item.name
---------   ---------
Danny       nail

How It Works #

• CROSS JOIN creates every possible combination of users and items (3 users × 2 items = 6 total pairs).
• We then filter out pairs that exist in item_user:
  • For Method 1: The LEFT JOIN keeps all pairs, and WHERE iu.item_id IS NULL selects only those with no matching order.
  • For Method 2: NOT EXISTS checks that there's no row in item_user linking the user and item, so only unordered pairs are kept.

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