Rows which do not exist in a table

Question

I have a lists of names John, Rupert, Cassandra, Amy, and I want to get names which are not exists in table: Cassandra, Amy

How should I write such query?

My table:

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
|  2 | John   | Doe       | 029  |
|  3 | Donald | Armstrong | 021  |
|  4 | Bob    | Gates     | 022  |
+----+--------+-----------+------+

Just run a query iterating the over the names and comparing them to a list of names you're looking to identify? — C_B, Jun 24 '15 at 09:58
I want to get names which are not exist in db and then insert them in these table and others. Now I do this in java — Dmitry1405, Jun 24 '15 at 10:06
C_B, I do this in java, but I want mysql do it. (*select * from users where name in (John, Rupert, Cassandra, Amy)* and them compare in in java — Dmitry1405, Jun 24 '15 at 10:10

score 2 · Accepted Answer · answered Jun 24 '15 at 10:40

2

Think in sets. You add names to a the result set with UNION ALL, you remove names from the result set with EXCEPT.

select 'John'
union all
select 'Rupert'
union all
select 'Cassandra'
union all
select 'Amy'
except
select name from mytable;

answered Jun 24 '15 at 10:40

Thorsten Kettner

89,309
7
49
73

You can replace the `union` with a simple `values ()` clause: `values ('John'), ('Rupert'), ('Cassandra'), ('Amy') except ...` – Jun 24 '15 at 10:43
@a_horse_with_no_name: You are right. I simply didn't know of the values clause. A beautiful solution. – Thorsten Kettner Jun 24 '15 at 10:46

score 1 · Answer 2 · answered Jun 24 '15 at 10:21

Build up a list of your names to check and do a left join to the users table:

with to_check (name) as (
   values 
     ('John'), ('Rupert'), ('Cassandra'), ('Amy')
)
select tc.name as missing_name
from to_check tc
  left join the_table tt on tt.name = tc.name
where tt.name is null;

SQLFiddle example: http://sqlfiddle.com/#!15/5c4f5/1

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

Hope your list is in form of table lets its be table b and your original table as a now SQL query goes like it

Select name from a where name not in (select name from b);

Think this will give you solution as per my understanding. Also if further details are required please comment.

Also its more important to search for an answer as it look like its a question from a book/Class. Please try out to find solution could have got much more information like link below

How to write "not in ()" sql query using join

edited May 23 '17 at 12:22

Community

1
1

answered Jun 24 '15 at 10:00

Vishal Sharma

3
4

i think you require distinct keyword in query – Ameya Deshpande Jun 24 '15 at 10:02
yes, you are correct however this will only make the query run more number of time. I totally agree with you best practice would be using distinct keywork – Vishal Sharma Jun 24 '15 at 10:07

Rows which do not exist in a table

3 Answers3