SQL查询需求：获取未存在于交易表中的指定用户ID及名称

Hey there! Let's work through this query to get the exact results you need.

First, the core goal is to pull id and name from the users table where the user's ID doesn't show up as either an investor_id (formatted as 23xx) or borrower_id (formatted as 100xx) in the transactions table. Here are two reliable, easy-to-read ways to write this:

Method 1: Using NOT EXISTS (Intuitive and Efficient) #

SELECT u.id, u.name
FROM users u
WHERE 
  -- Filter for the ID formats you specified
  (u.id LIKE '23__' OR u.id LIKE '100__')
  -- Ensure the ID isn't present as an investor_id in transactions
  AND NOT EXISTS (
    SELECT 1 
    FROM transactions t 
    WHERE t.investor_id = u.id
  )
  -- Ensure the ID isn't present as a borrower_id in transactions
  AND NOT EXISTS (
    SELECT 1 
    FROM transactions t 
    WHERE t.borrower_id = u.id
  )
  -- Add your additional investor-related filter here
  -- Example: AND u.investor_status = 'active' AND u.join_date >= '2023-01-01'
ORDER BY u.id;

Method 2: Using LEFT JOIN with IS NULL #

If you prefer join-based logic, this approach works just as well:

SELECT u.id, u.name
FROM users u
LEFT JOIN transactions t_investor ON u.id = t_investor.investor_id
LEFT JOIN transactions t_borrower ON u.id = t_borrower.borrower_id
WHERE 
  (u.id LIKE '23__' OR u.id LIKE '100__')
  AND t_investor.investor_id IS NULL
  AND t_borrower.borrower_id IS NULL
  -- Add your additional investor filter here
ORDER BY u.id;

Quick Breakdown: #

• The LIKE clauses handle the ID format checks: 23__ matches any 4-character ID starting with 23, and 100__ matches 5-character IDs starting with 100. Adjust the number of underscores if your ID lengths are different.
• Both queries will return the exact IDs you listed (2331, 2332, 10011, 10012) assuming those IDs exist in users and never appear in either the investor_id or borrower_id columns of transactions.
• For the additional investor-related filter you mentioned (which wasn't fully specified), just add the relevant condition to the WHERE clause. For example, if you need only investors with active accounts, you might add AND u.account_status = 'active' — tailor it to your actual schema rules.

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