MS Access中如何检查表1的值是否存在于表2中？

你需要判断table1的colName字段值是否包含在table2的colNewName字段值中，以下是不同数据库环境下的实现语句：

需求说明 #

• table1的colName字段值：Ford、Audi、Opel
• table2的colNewName字段值：87 Ford Carl、one9 Audi、_12br Opel 45X
• 期望输出包含table1值、对应table2值、以及是否存在的判断结果

具体实现 #

MySQL/MariaDB 写法 #

SELECT 
    t1.colName AS table1,
    t2.colNewName AS table2,
    CASE 
        WHEN t2.colNewName LIKE CONCAT('%', t1.colName, '%') THEN 'True'
        ELSE 'False'
    END AS Result
FROM table1 t1
JOIN table2 t2 ON t2.colNewName LIKE CONCAT('%', t1.colName, '%');

SQL Server 写法 #

SELECT 
    t1.colName AS table1,
    t2.colNewName AS table2,
    CASE 
        WHEN CHARINDEX(t1.colName, t2.colNewName) > 0 THEN 'True'
        ELSE 'False'
    END AS Result
FROM table1 t1
JOIN table2 t2 ON CHARINDEX(t1.colName, t2.colNewName) > 0;

PostgreSQL 写法 #

SELECT 
    t1.colName AS table1,
    t2.colNewName AS table2,
    CASE 
        WHEN POSITION(t1.colName IN t2.colNewName) > 0 THEN 'True'
        ELSE 'False'
    END AS Result
FROM table1 t1
JOIN table2 t2 ON POSITION(t1.colName IN t2.colNewName) > 0;

扩展：显示所有table1记录（含未匹配项） #

如果需要列出table1的所有值，哪怕在table2中找不到匹配项，可改用LEFT JOIN（以MySQL为例）：

SELECT 
    t1.colName AS table1,
    t2.colNewName AS table2,
    CASE 
        WHEN t2.colNewName LIKE CONCAT('%', t1.colName, '%') THEN 'True'
        ELSE 'False'
    END AS Result
FROM table1 t1
LEFT JOIN table2 t2 ON t2.colNewName LIKE CONCAT('%', t1.colName, '%');

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