Comparing two subqueries

Question

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?

Answer 1

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;

Answer 2

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.

Answer 3

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;

Answer 4

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

Answer 5

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]

Answer 6

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...)