Can't query names from a table that does not exist in another table

Question

I have two tables that are identical in fields. I want to query names in table2 that are not in table1. Both tables have name field as unique (primary key).

Here are the info. of my database design: My query is:

SELECT `table2`.`name` FROM `mydatabase`.`table2`, `mydatabase`.`table1` 
WHERE `table2`.`name` NOT IN (SELECT  `table1`.`name` FROM `mydatabase`.`table1`)
AND table2`.`name` NOT LIKE 'xyz%';

The output of SHOW CREATE TABLE <table name>:

For table1:

table1, CREATE TABLE `table1` (
  `name` varchar(500) NOT NULL,
  `ip` varchar(500) DEFAULT NULL,
  `type` varchar(500) DEFAULT NULL,
  `grade` varchar(500) DEFAULT NULL,
  `extended_ip` text,
  PRIMARY KEY (`name`),
  UNIQUE KEY `mydatabase_name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And table2:

tabl2, CREATE TABLE `table2` (
  `name` varchar(500) NOT NULL,
  `ip` varchar(500) DEFAULT NULL,
  `type` varchar(500) DEFAULT NULL,
  `grade` varchar(500) DEFAULT NULL,
  `extended_ip` text,
  PRIMARY KEY (`name`),
  UNIQUE KEY `mydatabase_name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The output of EXPLAIN <my query>:

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, table1, , index, , mydatabase_name_UNIQUE, 502, , 17584, 100.00, Using index
1, PRIMARY, table2, , index, , mydatabase_name_UNIQUE, 502, , 46264, 100.00, Using where; Using index; Using join buffer (Block Nested Loop)
2, SUBQUERY, table1 , index, PRIMARY,mydatabase_name_UNIQUE, mydatabase_name_UNIQUE, 502, , 17584, 100.00, Using index

EDIT: And I forgot to mention what happens is that the databse just crashes with my query. i am using mysql-workbench in Ubuntu 18. When I perform this query the whole workbench closes and I have to restart opening it again.

Madhur Bhaiya · Accepted Answer · 2018-09-27T17:37:27.540

Just do a LEFT JOIN on name, with table2 as your starting table, since you want to consider all the names from table2 which do not exist in table1. Names which don't exist in table1 will have a null value post the join. Note that this join based solution will be significantly faster than any subquery based approach.

Also, you should avoid comma (,) based implicit joins. It is old syntax, and you should use explicit JOIN based syntax. Read: Explicit vs implicit SQL joins

Also, it is a good habit to use Aliasing for better readability

Try the following:

SELECT t2.name 
FROM `mydatabase`.`table2` AS t2
LEFT JOIN `mydatabase`.`table1` AS t1 ON t1.name = t2.name 
WHERE t1.name IS NULL 
  AND t2.name NOT LIKE 'xyz%';

This works, too, and may be more efficient than using a subquery :) — billrichards, Sep 27 '18 at 17:57

score 0 · Answer 2 · answered Sep 27 '18 at 17:15

0

Try a subquery:

SELECT `table2`.`name` FROM `mydatabase`.`table2` WHERE `table2`.`name` NOT IN (SELECT `table1`.`name` FROM `mydatabase`.`table1`);

answered Sep 27 '18 at 17:15

billrichards

2,041
4
25
35

What about the rest of my query (I also have LIKE statement). can you complete your query please? – user9371654 Sep 27 '18 at 17:18
Also, I'm already using subquery like yours if you see my query. – user9371654 Sep 27 '18 at 17:19
If your goal is simply to query names in table2 that are not in table1 then my answer is what you want. Having both tables in the From is giving you problems. If you have additional criteria for table2.name then you can put it in a WHERE after the subquery: AND table2`.`name` NOT LIKE 'xyz%'; – billrichards Sep 27 '18 at 17:22
THanks for selecting my answer however please also consider the answer from @Madhur Bhaiya. In many cases it will be more efficient to LEFT JOIN and check for IS NULL. – billrichards Sep 28 '18 at 17:32

Can't query names from a table that does not exist in another table

2 Answers2