CODEWITHSUNDEEP
sqlsql-serverleft-join
nathan
nathan

Reputation: 83

SQL Can I have an IN or CONTAINS in the JOIN clause?

I am joining two SQL tables and I want to do something like this

LEFT JOIN uscities Ci 
        ON Customer_State = ci.state_id
        -- and Customer_City = ci.city
        and Customer_Zip in ci.zips

Is this possible in any way?

Upvotes: 0

Views: 1296

Answers (3)

David R Tribble
David R Tribble

Reputation: 12204

As others have stated here, you can essentially have any operator in a JOIN ON clause expression. You just have to be careful that the join expression only matches a single row of each item (table) being joined.

Having the ci.zips element be a string of space-separated zipcodes complicates things, but you can still search for (match) a specific customer zipcode within it with a simple LIKE clause:

left join uscities as ci
    on Customer_State = ci.state_id
--  and Customer_City = ci.city
    and ci.zips like '%' + Customer_Zip + '%'

If the ci.zips string is very long, though, this may not be that efficient.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can have IN in a JOIN clause. In fact, you can have any operator you want.

However, you cannot use IN on a single field. In fact, you simply should not be storing lists of things inside a string. It is very bad data modeling. You should have a separate table with one row per zip and per entity. That is the SQLish way to do things.

That said, I would recommend string_split():

LEFT JOIN uscities Ci 
ON c.Customer_State = ci.state_id AND
   c.Customer_Zip IN (SELECT value FROM STRING_SPLIT(ci.zips, ' '))

Upvotes: 3

Laughing Vergil
Laughing Vergil

Reputation: 3756

If ci.zips is a concatenated string of all zips, you can look inside it with the clause: 

and CHARINDEX(Customer_Zip, ci.zips) > 0
AND Len(Customer_Zip) = 5

However, this is a very inefficient action in SQL Server. It would be much better for you to split out this list of zip codes into a ZipCode table such as:

CREATE TABLE ZipCodes (
    ZipCode char(5) PRIMARY KEY
    )

Then, your condition is like:

and EXISTS (
    SELECT *
    FROM ZipCodes
    WHERE Customer_Zip = ZipCode
    )

More efficient, overall faster.

Upvotes: 1

Related Questions