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?

Answer 1

In SQL Server intersect

select * from table1 
intersect
select * from table1_backup

Answer 2

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

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

Answer 3

"intersect" is also part of standard SQL.

Inner join gives a different answer.

Answer 4

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

Answer 5

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

works

Answer 6

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

A Visual Explanation of SQL Joins

Answer 7

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.

Answer 8

subqueries?! really?

to get the intersection of table1 and table2:

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

Answer 9

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)