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

This would probably work. I'm using the table #test in place of your customers table and populating it with 1, 3 and 5. Then I'm searching for 1, 2 and 3.

CREATE TABLE #test 
(
    id int
)
GO

INSERT INTO #test
SELECT 1 UNION SELECT 3 UNION SELECT 5
GO

SELECT *
FROM ( SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 ) s
LEFT OUTER JOIN #test t ON s.id=t.id

Here's an example using your customers table:

SELECT s.SearchId, c.custid, c.name, c.email
FROM ( SELECT '1111' AS SearchId UNION SELECT '2222' 
    UNION SELECT '3333' UNION SELECT '4444' ) s
LEFT OUTER JOIN customers c ON s.SearchId=c.custid

Answer 2

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 3

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

Answer 4

You want you to put your custids that you a searching for in a table and join like so...

CREATE TABLE #temp
(
    custid varchar(4)
)

INSERT INTO #temp(employeeid) VALUES '1111'
INSERT INTO #temp(employeeid) VALUES '2222'
INSERT INTO #temp(employeeid) VALUES '3333'
INSERT INTO #temp(employeeid) VALUES '4444'

SELECT c.custid, name, email, t.custid  
FROM customers c
LEFT JOIN #temp t ON c.custid = t.custid

Answer 5

SQL server 2005, 2008

DECLARE @tmp TABLE (id varchar(8))

INSERT INTO @tmp ( id )
SELECT  '1111' UNION
SELECT  '2222' UNION
SELECT  '3333' UNION
SELECT  '4444' ;

SELECT c.custid, c.name, c.email  
FROM customers AS c
LEFT JOIN @tmp AS t ON t.id = customers.custid