EXTERNAL ENTRY using IN list?

Question

I would like to find hits and misses in the table, using only the list of elements, without creating a new table to contain the elements of the list and without any scripts. I make many special requests during the day, so that would be helpful.

Here is an example of what I'm using right now:

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

This returns all the entries in the client table, where the client IDs match those in the list that I provide.

I would like to find a way to return results like OUTER JOIN where I could see matches as well as misses.

FYI: I use MS SQL Server, but it would be useful to be able to do this in mySQL too. Thank!

Answer 1

, . , :

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)

get_id_table, ( - ).

Answer 2

. :

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

Match ( 4), . :

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

IsMatch "Match", .

, , .

Answer 3

, , . #test 1, 3 5. 1, 2 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

:

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 4

, custids, , ...

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