I have a table like this:

# names
 ---- ----------- 
| id |   name    |
 ---- ----------- 
| 1  | Jack      |
| 2  | Peter     |
| 3  | Alex      |
| 4  | Albert    |
| 5  | Martin    |
| 6  | Cristian  |
 ---- ----------- 

Also I have two values Ali, Alex. I need a query to give me Ali, since it's not exist in the table. Any idea how can I do that?

I know there is IN() function in MySQL, but it returns what exists (exactly the opposite of what I want)

SELECT name FROM names WHERE name IN ('Ali', 'Alex');
// current result => Alex
// expected result => Ali

Noted that, as you know NOT IN() won't be useful here. Any solution?

CodePudding user response：

Use a temporary table

1. union 'Ali' and 'Alex' as a temporary table,

select 'Ali' as name union select 'Alex'

then give the temporary table an alias, we say, t

2. then you can get 'Ali' via the sql below:

select * from (select 'Ali' as name union select 'Alex') t where not exists (select 1 from names n where n.name=t.name)

CodePudding user response：

Actually NOT IN can be used here, just diffenrently than you probably thought.

If you are really looking for 2 names, use this:

select 'Ali' where 'Ali' not in (select name from names)
union select 'Alex' where 'Alex' not in (select name from names)

If this is not flexible enough for your use case, you can consider creating a temporary table with these values.