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.

Mccurry

Have a look at this: codinghorror.com/blog/2007/10/… – Alfeus

@Mike Atlas: This has nothing to do with joins. It can be solved that way, but Jeff's blog post is not relevant. – Kiehl

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. – Alfeus

Check @Djebel answer at #2621882 – Squamulose

Answer 1

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

Kindergarten

Consider the case where record(firstname,lastname) only has (a, b) and data(firstname,lastname) only has (a, c) and (d, b). – Selvage

Answer 2

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.

Valdemar

Answer 3

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.

Guaranty

Answer 4

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

Factory

Answer 5

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"

Groundless

Answer 6

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)

Unwonted

Answer 7

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

Embolectomy

Answer 8

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

Bousquet

Answer 9

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.

Heartthrob