CODEWITHSUNDEEP
sql-servercase
Mienio
Mienio

Reputation: 99

Comparing two subqueries

I would ask my database: does second subquery contain result of first subquery.

Example:

select 
    case
        when (select [sub1_column] from [sub1]) IN (select [sub2_column] from [sub2]) then 'true' 
        else 'false'
    end;

This code failed with:

Subquery returned more than 1 value.

How to compare these subqueries?

EDIT:

My tables look like this:

[sub1_column]|
-------------+
|    'd'     |
|    'c'     |
|    'b'     |
-------------+

[sub2_column]|
-------------+
|    'a'     |
|    'b'     |
|    'c'     |
|    'd'     |
|    'e'     |
-------------+

The result is TRUE sub_1column values are in sub2_column

CASE statement was only an example. What if I would like to compare these subqueries in WHERE statement?

Upvotes: 2

Views: 8395

Answers (6)

Jesuraja
Jesuraja

Reputation: 3844

Try this:

Use EXCEPT. If no records found, both have same records. If records found, records mismatched.

SELECT 
    CASE
        WHEN
        NOT EXISTS
        (
            SELECT
                [sub1_column]
            FROM
                [sub1]
            EXCEPT
            SELECT
                [sub2_column]
            FROM
                [sub2]
        )           
        THEN 'true' 
        ELSE 'false'
    END;

Upvotes: 2

birdypme
birdypme

Reputation: 194

You need to join your two tables, and make sure that there are no rows on the second set that are not in the first set.

SELECT COUNT(1) FROM sub1
RIGHT OUTER JOIN sub2 ON sub1.sub1_column=sub2.sub2_column
WHERE sub2.sub2_column IS NULL

This will efficiently return >0 if all the sub1 column values are all included in sub2, and 0 otherwise. You can easily extend this to EXISTS() or CASE if you need (WHEN 0 ELSE...)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

Use EXISTS:

SELECT CASE WHEN EXISTS
(
    SELECT 1 FROM dbo.sub1 WHERE sub1_column IN(select sub2_column from sub2)
)
THEN 'true'
ELSE 'false' END AS Sub1ColumnExistsInSub2Column

Demo with your sample.

Upvotes: 0

JimmyB
JimmyB

Reputation: 12610

How about using a join?

select ...
from (select [sub1_column] from [sub1]) as a
join (select [sub2_column] from [sub2]) as b on a.[sub1_column] = b.[sub2_column]

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

Try this way:

select sub1_column,
       CASE WHEN sub2_column IS NULL THEN 'False' ELSE 'True' END AS sub2_column
from sub1 LEFT JOIN
     sub2 on sub1.sub1_column=sub2.sub2_column

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26856

You can use intersect to get records which exists in both queries:

select 
case
    when exists(
            select [sub1_column] from [sub1]
            intersect
            select [sub2_column] from [sub2]
           )
     then 'true' 
     else 'false'
end;

Upvotes: 0

Related Questions