CODEWITHSUNDEEP
sqlsql-serverunion
tt0686
tt0686

Reputation: 1849

Query without Union operator SQL

TABLE X
 col1,col2
   1  , 2
   1  , 7
   1  , 4
   1  , 8
   2  , 3
   2  , 1
   2  , 2
   3  , 1
   3  , 8
   3  , 9
   3  , 4
   4  , 5
   4  , 3
   4  , 2
   4  , 8
   4  , 4

I want to retrieve the col1 values that contains in the col2 the values 2 and 4 in this case it will retrieve the values 1 and 4 How can i accomplish this without using the UNION ALL operator ?

The query that i am using is 

select distinct col1
       from X as A
       where col1 = (
          select col1 from (
             select distinct col1
             from X as B
             where A.col1 = B.col1 and col2 = 2 
          union ALL
             select distinct col1
             from X as C
             where A.col1 = C.col1 and col2 = 4 
         ) D
       group by col1
       having count(col1) > 1
       )

It is returning the correct result but i guess is to performance expensive. Can anyone give me ideas about how to achieve the same result but without unions ?

Upvotes: 0

Views: 848

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

This problem is called Relational Division, here is one way to do so:

SELECT col1
FROM tablex
WHERE col2 IN (2, 4)
GROUP BY col1
HAVING COUNT(DISTINCT col2) >=2

The HAVING COUNT(col2) >=2 will ensure that the selected col1 must have both the two values 2 and 4 at least.

Upvotes: 2

PaF
PaF

Reputation: 3477

I think the best performance will come from inner joining the table with itself:

SELECT DISTINCT X1.col1
FROM X X1 INNER JOIN X X2 ON X1.col1=X2.col1
WHERE X1.col2=2 AND X2.col2=4

Upvotes: 1

Related Questions