基于查询结果的SQL条件连接实现求助（避免UNION）

First off, let's address the issue with your original SQL: using CASE directly in the WHERE clause to return boolean conditions won't work as expected. CASE returns a scalar value (like a string or number), not a true/false condition, so your current query will either throw a syntax error or evaluate incorrectly.

Here's a clean, efficient way to handle your conditional join logic without relying on UNION:

SELECT a.OPID, b.Name, b.Dept
FROM table1 a
-- Only join to table3 for D-prefixed subaccounts
LEFT JOIN table3 c 
  ON a.OPID LIKE 'D%' 
  AND a.OPID = c.OPID
-- Join to table2 using the correct ID based on account type
JOIN table2 b 
  ON b.ID = COALESCE(c.OPID, a.OPID);

Breakdown of how this works: #

• For D-prefixed subaccounts: The LEFT JOIN to table3 pulls in the linked numeric account ID. COALESCE will use this numeric ID to join to table2 (where personal data is stored).
• For numeric accounts: c.OPID will be NULL (since the LEFT JOIN doesn't match), so COALESCE falls back to using a.OPID to join directly to table2.ID.
• If a D-prefixed subaccount has no matching entry in table3, COALESCE will return NULL, and the JOIN to table2 will exclude that invalid record (since b.ID = NULL evaluates to false).

If you want to strictly enforce that all D-prefixed accounts have a valid link in table3 (and exclude any that don't), you can adjust the query to explicitly validate those records:

SELECT a.OPID, b.Name, b.Dept
FROM table1 a
LEFT JOIN table3 c 
  ON a.OPID LIKE 'D%' 
  AND a.OPID = c.OPID
JOIN table2 b 
  ON (a.OPID LIKE 'D%' AND c.OPID = b.ID)
  OR (a.OPID NOT LIKE 'D%' AND a.OPID = b.ID)
WHERE a.OPID NOT LIKE 'D%' OR c.OPID IS NOT NULL;

The WHERE clause here ensures that any D-prefixed account must have a valid match in table3 to be included in results.

Why this is better than UNION: #

• No need to duplicate your SELECT clause and join logic across two separate queries
• The query stays concise and easier to update if your schema or requirements change
• Database optimizers can often generate more efficient execution plans for a single query with conditional joins compared to a UNION of two queries

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