INTERSECT in MySQL

Question

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 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"

Thanks for any help.

Have a look at this: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html — Mike Atlas, Feb 19 '10 at 23:41
@Mike Atlas: This has nothing to do with joins. It can be solved that way, but Jeff's blog post is not relevant. — Aaronaught, Feb 19 '10 at 23:45
Yes, it does have to do with joins, and is relevant. It explains which SQL join is equivalent to an intersection of two data (inner join). From this, Jeremy should be able to figure out the standard SQL syntax for "intersection". I only posted this as a comment since others gave the exact answer below. — Mike Atlas, Feb 20 '10 at 20:27
Check @Djebel answer at http://stackoverflow.com/questions/2621382/alternative-to-intersect-in-mysql — Vikas Khunteta, May 23 '14 at 06:59

score 38 · Answer 1 · answered Feb 19 '10 at 23:35

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

Consider the case where record(firstname,lastname) only has (a, b) and data(firstname,lastname) only has (a, c) and (d, b). — Makmeksum, Jun 28 '18 at 05:22

score 16 · Answer 2 · answered May 15 '12 at 08:49

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.

score 5 · Answer 3 · answered Feb 19 '10 at 23:39

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:

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.

donatJ · Answer 4 · 2020-09-22T14:56:58.863

3

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

edited Sep 22 '20 at 14:56

answered Sep 21 '20 at 23:23

donatJ

3,105
3
32
51

score 1 · Answer 5 · answered Nov 08 '17 at 15:18

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)

score 1 · Answer 6 · answered Apr 03 '18 at 17:35

1

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

answered Apr 03 '18 at 17:35

Nazar Maksymchuk

48
11

score 1 · Answer 7 · answered Nov 27 '22 at 16:05

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"

score 0 · Answer 8 · edited Jul 15 '19 at 16:45

0

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

edited Jul 15 '19 at 16:45

Madalina Taina

1,968
20
26

answered Jul 15 '19 at 13:42

vinhNguyen.bmt

1
1

score 0 · Answer 9 · edited Nov 11 '22 at 09:59

0

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.

edited Nov 11 '22 at 09:59

Mustafa Poya

2,615
5
22
36

answered Nov 08 '22 at 10:00

Gin

51
3

INTERSECT in MySQL

9 Answers9

Linked

Related