SQL in-line equivalent to intersect

Question

Is there an inline sql command that is similar to or produces the same results as intersect? If not is there a way to rewrite the query so it is in one query rather than using intersect?

My current sql query is:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link IN (95,1)

But what I'm really after is:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link = 95 
INTERSECT 
SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link = 1

Thanks

Does your second query actually return any data? Surely, cs.classselection_link cannot be equal to 1 and 95 which is what, in effect, you are asking for in the second query. — Mutation Person
– Mutation Person, Commented Apr 13, 2011 at 16:07
Yes it does, basically classValues hold the class_link and the classSelection_link. Its basically a many-to-many relationship and I want all classes that contact both the passed classSelections. — lancscoder
– lancscoder, Commented Apr 13, 2011 at 16:10
Why do you not want to use INTERSECT? It exists for a reason, and that is cases like yours. There are other ways to write the query, but they will be a lot less clear than just using INTERSECT like you did in your example. Is there a reason you want to obfuscate the code? — JNK
– JNK, Commented Apr 13, 2011 at 16:24
@JNK: +1 for JNK's comment. Only reason I can think of is preformance. How well does SQL-Server optimize INTERSECT clauses? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Apr 13, 2011 at 16:34
And @Simon: using INTERSECT does not mean you have 2 queries. You still have 1 query (with 2 subqueries). — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Apr 13, 2011 at 16:37

ypercubeᵀᴹ · Accepted Answer · 2011-04-13 20:56:21Z

2

You can always do:

SELECT DISTINCT
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv1 ON c.Class_Link = cv1.Class_Link INNER JOIN 
  ClassSelection cs1 ON cv1.ClassSelection_Link = cs1.ClassSelection_Link

INNER JOIN
  ClassValues cv2 ON c.Class_Link = cv2.Class_Link INNER JOIN 
  ClassSelection cs2 ON cv2.ClassSelection_Link = cs2.ClassSelection_Link

WHERE 
    cs1.classselection_link = 95 
  AND
    cs2.classselection_link = 1

or with GROUP BY:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link IN (95,1)
GROUP BY
  c.Class_Link
HAVING
  COUNT(c.Class_Link) = 2

and since you don't really need the ClassSelection table:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link
WHERE 
  cv.ClassSelection_Link IN (95,1)
GROUP BY
  c.Class_Link
HAVING
  COUNT(c.Class_Link) = 2

As gbn pointed, I assumed that (Class_Link, ClassSelection_Link) is UNIQUE in table ClassValues.

If that is not true, then the HAVING clause at the last 2 queries should be changed to :

HAVING
  COUNT(DISTINCT cv.ClassSelection_Link) = 2

edited Apr 13, 2011 at 20:56

answered Apr 13, 2011 at 16:16

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gbn Over a year ago

What if there are 3 rows with classselection_link = 95 from the JOIN? Should be COUNT(DISTINCT cs.classselection) = 2, no?

ypercubeᵀᴹ Over a year ago

@gbn: You are right, I assumed that (Class_Link, ClassSelection_Link) is UNIQUE in table ClassValues. Your correction works in any case.

amit_g · Accepted Answer · 2011-04-13 16:11:17Z

2

May be using inner join...

SELECT
    *
FROM
    (
        SELECT 
          c.Log_Link 
        FROM 
          Classes c INNER JOIN 
          ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
          ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
        WHERE 
          cs.classselection_link = 95 
    ) T1
INNER JOIN
    (
        SELECT 
          c.Log_Link 
        FROM 
          Classes c INNER JOIN 
          ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
          ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
        WHERE 
          cs.classselection_link = 1
    ) T2
ON
    T1.Log_Link = T2.Log_Link

answered Apr 13, 2011 at 16:11

amit_g

31.3k8 gold badges67 silver badges124 bronze badges

Comments

gbn · Accepted Answer · 2011-04-13 18:19:20Z

2

INTERSECT and EXISTS are the same semantically. And you don't need so many tables

SELECT 
  c.Log_Link 
FROM 
  Classes c
WHERE 
  EXISTS (
      SELECT * FROM ClassValues cv
      WHERE c.Class_Link = cv.Class_Link AND cv.classselection_link = 95)
AND
  EXISTS (
      SELECT * FROM ClassValues cv
      WHERE c.Class_Link = cv.Class_Link AND cv.classselection_link = 1)

edited Apr 13, 2011 at 18:19

answered Apr 13, 2011 at 18:08

gbn

434k84 gold badges602 silver badges690 bronze badges

Comments

Pete M · Accepted Answer · 2011-04-13 16:17:28Z

1

I'm pretty sure you just want distinct. You're trying to get a unique set of c.Log_Link that matches either criteria, correct? If so:

SELECT DISTINCT
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link IN (95,1)

Edit: I see your clarification. I think joining derived tables as amit_g suggested would be the way to go.

I'd tweak it just a bit though:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
  INNER JOIN (
        SELECT 
          c.Log_Link 
        FROM 
          Classes c INNER JOIN 
          ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
          ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
        WHERE 
          cs.classselection_link = 1
    ) T2 ON c.Log_Link = T2.Log_Link
WHERE 
  cs.classselection_link = 95

edited Apr 13, 2011 at 16:17

answered Apr 13, 2011 at 16:10

Pete M

2,04811 silver badges17 bronze badges

1 Comment

lancscoder Over a year ago

Thanks but distinct doesn't work. I want it to return all the Log_links associated to 95 and 1 whereas DISTINCT returns all the log_links just removing any duplicates.

Collectives™ on Stack Overflow

SQL in-line equivalent to intersect

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related