CODEWITHSUNDEEP
mysqlselectwhere-clause
vshale
vshale

Reputation: 167

SELECT WHERE IN - mySQL

let's say I have the following Table:

 ID, Name 
 1, John 
 2, Jim 
 3, Steve 
 4, Tom

I run the following query

SELECT Id FROM Table WHERE NAME IN ('John', 'Jim', 'Bill');

I want to get something like:

ID
1
2
NULL or 0

Is it possible?

Upvotes: 13

Views: 103988

Answers (3)

ChaiNavawongse
ChaiNavawongse

Reputation: 199

How about this?

SELECT Id FROM Table WHERE NAME IN ('John', 'Jim', 'Bill')
   UNION
SELECT null;

Upvotes: 7

marco
marco

Reputation: 580

The question is a bit confusing. "IN" is a valid operator in SQL and it means a match with any of the values (see here ):

SELECT Id FROM Table WHERE NAME IN ('John', 'Jim', 'Bill');

Is the same as:

SELECT Id FROM Table WHERE NAME = 'John' OR NAME = 'Jim' OR NAME = 'Bill';

In your answer you seem to want the replies for each of the values, in order. This is accomplished by joining the results with UNION ALL (only UNION eliminates duplicates and can change the order):

SELECT max(Id) FROM Table WHERE NAME = 'John' UNION ALL
SELECT max(Id) FROM Table WHERE NAME = 'Jim' UNION ALL
SELECT max(Id) FROM Table WHERE NAME = 'Bill';

The above will return 1 Id (the max) if there are matches and NULL if there are none (e.g. for Bill). Note that in general you can have more than one row matching some of the names in your list, I used "max" to select one, you may be better of in keeping the loop on the values outside the query or in using the (ID, Name) table in a join with other tables in your database, instead of making the list of ID and then using it.

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26333

Start by creating a subquery of names you're looking for, then left join the subquery to your table:

SELECT myTable.ID
FROM (
  SELECT 'John' AS Name
  UNION SELECT 'Jim'
  UNION SELECT 'Bill'
) NameList
LEFT JOIN myTable ON NameList.Name = myTable.Name

This will return null for each name that isn't found. To return a zero instead, just start the query with SELECT COALESCE(myTable.ID, 0) instead of SELECT myTable.ID.

There's a SQL Fiddle here.

Upvotes: 6

Related Questions