Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Mark Leighton Fisher (4252)

Mark Leighton Fisher
  (email not shown publicly)
http://mark-fisher.home.mindspring.com/

I am a Systems Engineer at Regenstrief Institute [regenstrief.org]. I also own Fisher's Creek Consulting [comcast.net].
Friday November 09, 2007
12:51 PM

SQL Intersect by ANDing IN Clauses

[ #34866 ]

Set intersect in SQL does not require an INTERSECT operator. To express a set intersection in SQL, you can AND together IN clauses. This makes sense, as one way to describe a set intersection is to say:

"... it is a member of one set AND another set AND another set AND so on ..."

How do you express "a member of one set"? By saying "WHERE field IN list_or_subselect". The resulting SQL for a set intersection operation looks like:

SELECT
  field1, ...
FROM
  table1, ...
WHERE
  field_of_interest IN (SELECT fieldX ...
AND
  field_of_interest IN ('x', 'y-2', 42 ...
AND
  ...

(Thanks to Kim Burchett and his Why Faceted Navigation is Hard for the initial explanation I saw of this technique.)

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.