OUTER JOIN using IN list?

Question

I'd like to find the hits and misses in a table using only a list of items without having to create a new table to contain the list items and without using any scripting. I do many ad-hoc queries throughout the day so this would be useful.

Here's an example of what I'm using now:

SELECT custid, name, email  
FROM customers  
WHERE custid IN  
('1111', '2222', '3333', '4444')  

This returns all the entries in customers table where the customer IDs match the one in the list I provide.

I'd like to find a way to return results like an OUTER JOIN, where I could see the matches as well as the misses.

FYI: I'm using MS SQL Server but it would be useful to be able to do this in mySQL as well. Thanks!

Answer 1

There are several ways to do this. Here are two:

SELECT C1.custid, name, email, C2.CustID As Match FROM customers As C1 left join (SELECT custid FROM customers
WHERE custid IN
('1111', '2222', '3333', '4444')) As C2 on C1.custid=C2.custid

There will be a number (one of the 4) in the Match column if it's one of the numbers. Or:

SELECT custid, name, email, Case When custid in ('1111', '2222', '3333', '4444') Then 'Match' Else '' End As IsMatch FROM customers

The IsMatch column will say "Match" if it's one of the four values.

The second one will usually be faster.

Answer 2

Use a user-defined function to generate a table from a comma-separated list of values. Then you can do an outer join to the list, like this:

select customers.custid, name, email
  from customers
    left outer join dbo.get_id_table('1111', '2222', '3333', '4444') as ids
      on (customers.custid = ids.custid)

There are several versions of a 'get_id_table' type function posted here and elsewhere on the web (sorry - I can't come up with a link right now).