如何通过单查询实现两表type字段关联（不可改表/存储过程）

Absolutely! You can absolutely make this work with a single query—no table changes or stored procedures required. The trick is to embed your type field equivalence logic directly into the JOIN condition itself, since you can't rely on a separate lookup table. Here are two common scenarios and how to handle them:

1. Fixed Known Equivalences #

If you have a clear, static list of which type values from table1 map to which values in table2, use a CASE statement or explicit OR conditions in your JOIN.

Using CASE Statement #

This is clean if you have a lot of mappings:

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 ON 
  CASE t1.type
    WHEN 'customer' THEN 'client'  -- table1's 'customer' = table2's 'client'
    WHEN 'vendor' THEN 'supplier'   -- table1's 'vendor' = table2's 'supplier'
    WHEN 'internal' THEN 'staff'    -- Add as many mappings as needed
    ELSE t1.type                    -- Fallback for exact matches
  END = t2.type;

Using OR Conditions #

If you want to leverage index usage (when available) for better performance, explicit OR pairs might be better:

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 ON 
  (t1.type = 'customer' AND t2.type = 'client')
  OR (t1.type = 'vendor' AND t2.type = 'supplier')
  OR (t1.type = t2.type);  -- Include exact matches here

2. Rule-Based Equivalences #

If the type mappings follow a pattern (like case differences, prefixes/suffixes, or string transformations), use database string functions to normalize both fields before comparing.

Example: Case Insensitive Matching #

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 ON LOWER(t1.type) = LOWER(t2.type);

Example: Remove Prefix/Suffix #

If table1 uses a prefix like type_ that table2 doesn't:

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 ON SUBSTRING(t1.type, 6) = t2.type; -- Skip first 5 chars ('type_')

Example: Replace Substrings #

If certain keywords are swapped between tables:

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 ON REPLACE(t1.type, 'old_', 'new_') = t2.type;

Key Notes #

• If your equivalence list is very long, the CASE approach will keep your query more readable than a giant list of ORs.
• Performance might take a hit if you're using functions on indexed columns (since indexes can't be used directly). If this is an issue, check if your database supports function-based indexes—but since you can't modify tables, this might not be an option.
• For more complex logic, you could even use a subquery to normalize the type field first, then join the results, but the above approaches are more direct.

内容的提问来源于stack exchange，提问作者J. Ordaz