MySQL NOT IN versus LEFT JOIN
| Author: | Osiris Alejandro Gomez <osiux@osiux.com.ar> |
|---|---|
| Copyright: | CreativeCommons CC-BY-SA [1] |
| Date: | 2010-05-10 19:40 |
Detailed Example
Create tables
create table a (id int null, name varchar(10) null); create table b (id int null, name varchar(10) null);
Insert Values
insert into a (id,name) values (1,'a'); insert into a (id,name) values (2,'b'); insert into a (id,name) values (3,'c'); insert into a (id,name) values (4,'d'); insert into a (id,name) values (5,'e'); insert into a (id,name) values (6,'e'); insert into a (id,name) values (7,'f'); insert into a (id,name) values (8,'g'); insert into a (id,name) values (9,'h'); insert into b (id,name) values (6,'e'); insert into b (id,name) values (7,'f'); insert into b (id,name) values (8,'g'); insert into b (id,name) values (9,'h');
View table a
select a.id, a.name from a +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | | 6 | e | | 7 | f | | 8 | g | | 9 | h | +------+------+
View table b
select b.id, b.name from b +------+------+ | id | name | +------+------+ | 6 | e | | 7 | f | | 8 | g | | 9 | h | +------+------+
NOT IN
select a.id, a.name from a where a.id not in (select id from b) +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +------+------+ 5 rows in set (0.01 sec)
LEFT JOIN
select a.id, a.name from a left join b on a.id=b.id where b.id is null; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +------+------+ 5 rows in set (0.00 sec)
| [1] | http://creativecommons.org/licenses/by-sa/2.5/ar/ |