How do I get my INTERSECT code to include all rows?-CodePudding

I am working with two tables:

Table1

Name    Team    ID
Robinho Brazil  1
Ronaldo Brazil  2
Totti   Italy   3
Baggio  Italy   4
Rooney  England 5

Table2

ID  Football_Club   Address
1   Chelsea         London
3   Fulham          London

I would like a new table with all columns included but to only include the two rows where the two tables intersect. I am using the following SQL Query which is very wrong:

SELECT id,
       NAME,
       team
FROM   table1
INTERSECT
SELECT id,
       football_club,
       address
FROM   table2

How can I rewrite this to bring about the correct result using INTERSECT?

CodePudding user response：

You want an INNER JOIN rather than an INTERSECT. Replace * with the columns you need using the table aliases.

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.id = t2.id;