ПЕРЕСЕЧЕНИЕ в MySQL

Вы можете использовать внутреннее соединение для фильтрации строк, которые имеют совпадающую строку в другой таблице:

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 запросов, и производительность приемлема.

Все, что вы делаете, это «ОБЪЕДИНЯЙТЕ ВСЕ» каждый из запросов, которые вы хотите пересечь, и смотрите, какие из них вы получили каждый раз.

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

Общей заменой INTERSECT в MYSQL является внутреннее соединение:

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, у вас может быть 2 альтернативы: внутреннее соединение и in. Это решение с in:

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);