INTERSECT in MySQL

I have two tables, records and data. records has multiple fields (firstname, lastname, etc.). Each of these fields is a foreign key for the data table where the actual value is stored. I need to search on multiple record fields.

Below is an example query using INTERSECT, but I need one that works in MySQL.

SELECT reco开发者_运维问答rds.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"

Thanks for any help.

---

You can use an inner join to filter for rows that have a matching row in another table:

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"

One of many other alternatives is an in clause:

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

---

I think this method is much easier to follow, but there is a bit of an overhead associated with it because you are loading up lots of duplicate records initially. I use it on a database with about 10000-50000 records and typically intersect about 5 queries and the performance is acceptable.

All you do is "UNION ALL" each of the queries you want to intersect and see which ones you got every time.

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 

So if we get the same record in both queries, it's count will be 2 and the final wrap-around query will include it.

---

Use joins instead:

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'

Here's some test data:

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

Expected result:

2

The test data is probably not useful for the poster, but might be useful for voters who want to check solutions to see that they work correctly, or people who want to submit answers so that they can test their own answers.

---

I'm a little late to the party, but I think the cleanest and best way to fully emulate INTERSECT is:

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

---

A general replacement for INTERSECT in MYSQL is inner join:

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)

Or for your case specifically:

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

---

Since Mysql doesn't support INTERSECT, you may have 2 alternatives: inner join and in. This is a solution with 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);

---

You can try this:

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

But there is no optimization on this option.

---

Since 10 November 2022, MySQL has added support to the INTERSECT operator with the updates of version 8.0.31.

You can now feel free to use it:

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"