What is the standard SQL Query to retrieve the intersection of tables?

Question

Selecting the union:

select * from table1 
union 
select * from table1_backup

What is the query to select the intersection?

Trying to be cross-db compliant. Mainly focusing on MySQL and SQLServer. — Peter Turner, Sep 29 '08 at 14:17

score 9 · Answer 1 · answered Sep 29 '08 at 14:08

9

In SQL Server intersect

select * from table1 
intersect
select * from table1_backup

answered Sep 29 '08 at 14:08

Tom Ritter

99,986
30
138
174

Shoot, so that's just a SQL Server answer, is there no SQL Server/MySQL cross-db query? – Peter Turner Sep 29 '08 at 14:10
1

According to blog posts and listservs from 2005 I found on google, MySQL doesn't support the straight intersect notation, and instead suggests Inner Joins or subqueries. – Tom Ritter Sep 29 '08 at 14:13
1

The question doesn't state that a MySQL solution is required. Be aware that an Interest is also an implicit DISTINCT (same as UNION and MINUS) – David Aldridge Sep 29 '08 at 15:38

workmad3 · Answer 2 · 2008-09-29T17:43:46.403

6

SELECT *
FROM table1
WHERE EXISTS
(SELECT *
FROM table1_backup
WHERE table1.pk = table1_backup.pk)

works

edited Sep 29 '08 at 17:43

answered Sep 29 '08 at 14:15

workmad3

25,101
4
35
56

This looks like a good answer, but not as abstract as the one I accepted. It's probably faster too. – Peter Turner Sep 29 '08 at 14:21
For my purposes, I didn't need the where clause. – Peter Turner Sep 29 '08 at 14:31
2

You need a WHERE clause otherwise it doesn't make sense. You'll just return all rows from table1 if any rows exist in table1_backup. Otherwise you'll get zero rows. – beach Aug 06 '09 at 23:49

score 2 · Answer 3 · edited Jan 15 '16 at 00:34

2

For questions like this, I tend to go back to this visual resource:

A Visual Explanation of SQL Joins

edited Jan 15 '16 at 00:34

henrym

2,565
1
16
14

answered Sep 30 '08 at 03:03

Jeremy Wadhams

1,744
18
26

cesar · Answer 4 · 2009-08-14T09:49:26.057

here is a solution for mySQL:

CREATE TABLE table1(
id INT(10),
fk_id INT(10),
PRIMARY KEY (id, fk_id),
FOREIGN KEY table1(id) REFERENCES another_table(id),
FOREIGN KEY table1(fk_id) REFERENCES other_table(id)
);

SELECT table1.* FROM table1 as t0
INNER JOIN table1 as a ON (t0.id = a.id and fk_id=1)
INNER JOIN table1 as b ON (t0.id = b.id and fk_id=2)
INNER JOIN table1 as c ON (t0.id = c.id and fk_id=3)
ORDER BY table1.id;

Basically you have an table of mathematical subsets (ie. 1={1, 2 ,3}, 2={3, 4, 2}, ... , n={1, 4, 7}) with an attribute id, which is the set number, and fk_ id, which references a PRIMARY KEY of a table of elements, the superset (meaning possible values for the numbers in the curly braces). For those not mathematically inclined, let's pretend you have a table, 'other_ table', which is a list of items, and another table, 'another_ table', which is a list of transaction numbers, and both tables form a many-to-many relationship, thus producing 'table1'. now let's pretend you wanted to know the id's in 'another_ table' which had items 1, 2, and 3. that's the query to do it.

score 1 · Answer 5 · answered Sep 29 '08 at 14:09

1

inner join i think: suppose T1 and T2 have the same structure:

select T1.* from T1 inner join T2 on T1.pkField = T2.pkField

answered Sep 29 '08 at 14:09

stefano m

4,094
5
28
27

The union is useful because it doesn't add a extra fields. It treats two tables as the same table when used. I want the same thing, except I want I to see which records are exactly the same in case something messed up in running a backup. – Peter Turner Sep 29 '08 at 14:12

score 1 · Answer 6 · answered Sep 29 '08 at 14:11

1

"intersect" is also part of standard SQL.

Inner join gives a different answer.

answered Sep 29 '08 at 14:11

Walter Mitty

18,205
2
28
58

So what would the query be? select * from table1 intersect select * from table2 gives me an error in mysql – Peter Turner Sep 29 '08 at 14:13

score 0 · Answer 7 · answered Aug 15 '10 at 11:37

0

An intersect on two identical tables a and b can be done in this manner:

SELECT a.id, a.name
FROM a INNER JOIN b
USING (id, name)

answered Aug 15 '10 at 11:37

ovais.tariq

2,627
17
12

Stephen Wuebker · Answer 8 · 2009-12-29T15:37:07.930

-1

subqueries?! really?

to get the intersection of table1 and table2:

SELECT * FROM table1, table2 WHERE table1.pk=table2.pk;

edited Dec 29 '09 at 15:37

answered Dec 29 '09 at 14:16

Stephen Wuebker

189
6

1

That's not the intersection, that's just a join. Unions work with two identical datasets, I was looking for a related function. You're right though, you could join on each field. The -1 is just for using the Lord's name in vain. – Peter Turner Dec 29 '09 at 14:54

score -4 · Answer 9 · answered Sep 29 '08 at 14:13

-4

select distinct * from (select * from table1 union select * from table1_backup)

answered Sep 29 '08 at 14:13

James Curran

101,701
37
181
258

Excellent, this is the query I was looking for, stackoverflow is awesome, only took 5 minutes and was a lot more fun than Google-ing. This answer is perfect for me in every way except it needs to have "as table_x" at the end to not get a key violation in my version of SQL. – Peter Turner Sep 29 '08 at 14:16
This doesn't return an intersect... it returns every single row in each of the tables, and filters out duplicates. – Tom Ritter Sep 29 '08 at 14:26

What is the standard SQL Query to retrieve the intersection of tables?

9 Answers9

Linked