Correlated Subquery in SQL Server 2014

I am running a correlated subquery to find out the listing of vendors (by Vendor Name) that are in different cities, states, i.e. we want to know the vendors that do not have a common city and state with other vendors. It seemed like a self-join was the thing to do.

Only hints if possible, please.

The Vendors table is:

Vendors(VendorID P, VendorCity, VendorState, VendorName,...)

This is what I have:

Select VendorName, VendorCity, VendorState from Vendors AS V1 where
         VendorCity, VendorState NOT IN (Select VendorCity, VendorState FROM
          Vendors AS V2 where V2.VendorID <> V1.VendorID)

This is the error message I get:


Msg 4145, Level 15, State 1, Line 2 An expression of non-boolean type
specified in a context where a condition is expected, near ','.

I don't see why there is a reference to Boolean types, since this is not an EXISTS or other related query.

The syntax for IN(...) is:

test_expression [ NOT ] IN 
    ( subquery | expression [ ,...n ]    )

With:

test_expression

Is any valid expression.
subquery

Is a subquery that has a result set of one column. This column must have the same data type as test_expression.
expression[ ,... n ]

Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

This means that only 1 column is allowed on both side. Even a list of expressions is considered as 1 dummy table with 1 column for expression values similar to:

SELECT exp FROM (values(exp1), (exp2), ...) as X(exp)

With 2 columns you can use a subquery with EXISTS:

Select VendorName, VendorCity, VendorState 
FROM Vendors AS V1 
WHERE NOT EXISTS (
    SELECT 1 
    FROM Vendors AS V2 
    WHERE V2.VendorID <> V1.VendorID
        AND VendorCity = VendorCity
        AND VendorState = VendorState
)

Overall this subquery always return 0 row or 1 or more rows with 1 because it only needs to know if there is 1 or more rows with the same City and State.
The value is not important because it only looks at the number of row (=> if 1 or more rows EXISTS), hence the SELECT 1. The test has already been done in the inner WHERE clause.

I guess that VendorId is the PK. An index on VendorCity and VendorState would help.

Since you are learning and if you don't already know it, you can also look at the usage of APPLY (CROSS APPLY and OUTER APPLY). I let you try it and play with it.

StackExchange Database Administrators Q#131350, answer score: 6