SQL新手技术求助：如何筛选包含另一表列关键词的数据

Hey there! Let's sort out this SQL issue—it's super common when you're just starting out, so don't feel stuck! Your core goal is to pull rows from tableA.columnA that contain any of the keywords in tableB.columnB, and the problem with your original query is that the subquery returns multiple values (you can't concatenate a string with a list of strings directly).

Here are two straightforward, efficient ways to get what you need:

方法1：用JOIN搭配LIKE #

This first method joins the two tables on the condition that columnA contains the keyword from columnB, then uses DISTINCT to avoid duplicate rows (since one columnA entry might match multiple keywords):

SELECT DISTINCT a.columnA
FROM tableA a
JOIN tableB b 
  ON a.columnA LIKE CONCAT('%', b.columnB, '%');

小提示： #

• CONCAT('%', b.columnB, '%') wraps each keyword in wildcard characters, so we catch any occurrence of the keyword anywhere in columnA.
• If you're using SQL Server, you can swap CONCAT for string concatenation with +: a.columnA LIKE '%' + b.columnB + '%'
• DISTINCT makes sure you don't get duplicate entries from tableA if a single row matches multiple keywords from tableB.

方法2：用EXISTS子查询 #

If you prefer a subquery-based approach (and this can be faster with large datasets), use EXISTS to check if there's at least one keyword in tableB that matches the current columnA row:

SELECT columnA
FROM tableA a
WHERE EXISTS (
    SELECT 1
    FROM tableB b
    WHERE a.columnA LIKE CONCAT('%', b.columnB, '%')
);

小提示： #

• EXISTS stops searching as soon as it finds a matching keyword for a row, which saves time when working with big tables.
• You don't need DISTINCT here because each row from tableA is only returned once, even if it matches multiple keywords.

For your sample data, both queries will spit out exactly what you want:

'This apple is red'
'This ball is round'

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