How to make an "in" statement inclusive-only in SQL

Question

EDITED:
Read table_row as table_column (Dudu Markovitz)

---

I have a subset of values that I want to evaluate against a row in SQL.

Select count(*) cnt  
from table_name c
where c.table_row in ('value 1','value 2', 'value 3')
and other statements;

This works fine as long as I only want to determine if "table_row" contains one of those three values.

What I want, however, is to determine if table_row contains only those values.

Is there any SQL statement that can perform an inclusive-only 'in' statement, so that for the "cnt" in the above query, I will only get a result if 'table_row' is in the set indicated by the query?

Answer 1

Maybe this would do?

select count(*) cnt  
from table_name c
where
not exists (
    select * table_name t
    where COALESCE(table_row,'-1') != 'value 1' 
    and COALESCE(table_row,'-1') != 'value 2' 
    and COALESCE(table_row,'-1') != 'value 3'
)
and other statements;

It looks like of weird as the subquery is not linked to the parent query but... It should work.

Answer 2

You can use Having clause. Below is a pseudocode.

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
GROUP BY LastName
HAVING LastName in ('Davolio','Fuller')

Answer 3

This is what you might looking for -
Show the result of the count only if contains no other value then 'value 1', 'value 2' and 'value 3'

select  count(*) as cnt  
from    table_name c
where   c.mycol in ('value 1','value 2', 'value 3')
        -- ...and other statements
having  count(*) = 
        count(case when c.table_row in ('value 1','value 2', 'value 3') then 1 end) 

And you might also just want to see the counts

select  count(*)                                                                    as cnt  
       ,count(case when c.table_row in ('value 1','value 2', 'value 3') then 1 end) as cnt_val
from    table_name c
where   c.mycol in ('value 1','value 2', 'value 3')
        -- ...and other statements