when to use intersect in a query

Question

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"?

Do you know that `FROM MovieStar, MovieExec` is CROSS JOIN (cartesian product)? — Lukasz Szozda, Aug 31 '15 at 14:04
The only tricky way is execution plan and statistics, 2 queries may be equivalent in result but the way they get it may vary. So always compare execution plan, time and resource needed to achieve what you want. — Lukasz Szozda, Aug 31 '15 at 14:07
Basically FROM MovieStar, MovieExec means that you will get NxM records in result. Run `SELECT name, address FROM MovieStar, MovieExec` and see how many records you get. You should avoid this. — Lukasz Szozda, Aug 31 '15 at 14:09
Thank you for your time, i will try more examples to compare with :) — Zisis Diamantis, Aug 31 '15 at 14:10
The thing is that this is an example from a book so i dont actually have a real database to see what happens, so maybe i should try a fuctional example — Zisis Diamantis, Aug 31 '15 at 14:12

Lukasz Szozda · Accepted Answer · 2015-08-31T14:30:25.620

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

I think i understand, so with the first solution i would get only the records which are both female and networth > 1000000 but in the second one i will get both records of females and records of networth > 10000000.Thank you! — Zisis Diamantis, Aug 31 '15 at 14:21
@ZisisDiamantis See m updated answer and check sqlfiddle example. In both cases you will get female and networth > 1000000 but in the second you will get the same answer multiple times, it's how CROSS JOIN works, if you don't want this you need to use `SELECT DISTINCT ms.name, ms.address` — Lukasz Szozda, Aug 31 '15 at 14:31

when to use intersect in a query

1 Answers1