CODEWITHSUNDEEP
sqlintersect
Zisis Diamantis
Zisis Diamantis

Reputation: 131

when to use intersect in a query

i am a bit comfused about when do i have to use intersect in sql.The example that i am given is the following:

I have two tables:

 MovieStar(name, address, gender, birthdate)
 MovieExec(name, address, cert#, netWorth)

The example asks to find the name and address of all female actors who also are a movie executor and have networth over 10000000.The solution of the example in the book is the following:

(SELECT name, address
 FROM MovieStar
 WHERE gender = 'F')
   INTERSECT
 (SELECT name, address
  FROM MovieExec
  WHERE netWorth > 10000000);

So my problem is why i have to use INTERSECT while i could use the "AND" operator like:

SELECT name, address
FROM MovieStar, MovieExec
WHERE gender = 'F' AND netWorth > 10000000

Is there any tricky way to figure out when is better to use INTERSECT or "AND"?

Upvotes: 0

Views: 128

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

Use INTERSECT when it suits you and you get correct results. Second always compare execution plan and statistics, because the way you get result may vary.

SqlFiddleDemo

1) 

 SELECT name, address
 FROM MovieStar
 WHERE gender = 'F'
 INTERSECT
 SELECT name, address
 FROM MovieExec
 WHERE netWorth > 10000000;

It means take name and addresses from MovieStar where gender is 'F', take name and address from MovieExec where networth > 100000 and find records which are in both sets.

2)

SELECT ms.name, ms.address
FROM MovieStar AS ms, MovieExec AS me
WHERE gender = 'F' AND netWorth > 10000000

It means that you generate CROSS JOIN Cartesian Product(MxN records) and then take only records where gender = 'F' AND netWorth > 10000000

I guess that the first approach will returns result faster and use less memory(but Query Optimizer can do a lot).

When you should use INTERSECT:

  • you want to get intersection of both sets and you cannot JOIN them explicitly

Upvotes: 3

Related Questions