您可以使用内部链接来筛选在另一个数据表中有相符数据列的数据列：

SELECT DISTINCT records.id 
FROM records
INNER JOIN data d1 on d1.id = records.firstname AND data.value = "john"
INNER JOIN data d2 on d2.id = records.lastname AND data.value = "smith"

in子句是许多其他替代方法之一：

SELECT DISTINCT records.id 
FROM records
WHERE records.firstname IN (
    select id from data where value = 'john'
) AND records.lastname IN (
    select id from data where value = 'smith'
)

我认为这种方法更容易遵循，但有一点开销，因为你开始加载大量的重复记录。我用它在一个数据库约10000-50000记录，通常相交约5查询和性能是可以接受的。
您所要做的就是“UNION ALL”每个要相交的查询，并查看每次都得到了哪些查询。

SELECT * From (

    (Select data1.* From data1 Inner Join data2 on data1.id=data2.id where data2.something=true)
    Union All
    (Select data1.* From data1 Inner Join data3 on data1.id=data3.id where data3.something=false)

) As tbl GROUP BY tbl.ID HAVING COUNT(*)=2

因此，如果我们在两个查询中得到相同的记录，它的计数将是2，最终的环绕查询将包括它。

改用联接：

SELECT records.id
FROM records
JOIN data AS D1 ON records.firstname = D1.id
JOIN data AS D2 ON records.lastname = D2.id
WHERE D1.value = 'john' and D2.value = 'smith'

以下是一些测试数据：

CREATE TABLE records (id INT NOT NULL, firstname INT NOT NULL, lastname INT NOT NULL);
INSERT INTO records (id, firstname, lastname) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1),
(4, 2, 2);

CREATE TABLE data (id INT NOT NULL, value NVARCHAR(100) NOT NULL);
INSERT INTO data (id, value) VALUES
(1, 'john'),
(2, 'smith');

预期结果：

2

测试数据可能对海报没有用，但对想要检查解决方案以查看其是否正确工作的投票人，或想要提交答案以便测试自己答案的人可能有用。

我有点晚了，但我认为完全模拟INTERSECT的最干净、最好的方法是：

SELECT * FROM
( SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john" ) x1
NATURAL JOIN
( SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith" ) x2

在MYSQL中INTERSECT的一般替代是内部连接：

SELECT DISTINCT * FROM 
(SELECT f1, f2, f3... FROM table1 WHERE f1>0)
INNER JOIN
(SELECT f1, f2, f3... FROM table2 WHERE f1>0)
USING(primary_key)

或者针对您的具体情况：

SELECT DISTINCT * FROM 
(SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john") query1
INNER JOIN
(SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith") query2
USING (id)

SELECT t.id FROM table t WHERE NOT EXISTS (SELECT t2.id, FROM table2 t2 WHERE t2.id = t1.id)
https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

由于Mysql不支持INTERSECT，您可能有两种选择：内部联接和在中。这是一个在中的解决方案：

SELECT records.id FROM records, data 
WHERE data.id = records.firstname AND data.value = "john"
    AND records.id in (SELECT records.id FROM records, data 
    WHERE data.id = records.lastname AND data.value = "smith);

您可以尝试以下操作：

SELECT 
        r.id, GROUP_CONCAT(data.value) AS gcValue
FROM records r
LEFT JOIN data d ON d.id = records.firstname
GROUP BY records.firstname
HAVING FIND_IN_SET('john', gcValue) AND FIND_IN_SET('smith', gcValue);

但此选项没有优化。

自2022年11月10日起，MySQL在updates of version 8.0.31中增加了对INTERSECT运算符的支持。
您现在可以随意使用它：

SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john"
INTERSECT
SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith"