CODEWITHSUNDEEP
mysqlnot-exists
Suri
Suri

Reputation: 209

Query with NOT EXIST in MySQL does not return expected result

I create two tables Types and SuperTypes which have column with name types and type respectively. I create SuperTypes table based on distinct type of the Types table but when I executed following query I got these result.

mysql> select count(distinct type) from SuperTypes;
+----------------------+
| count(distinct type) |
+----------------------+
|                 1302 |
+----------------------+



mysql> select count(distinct types) from Types;
+-----------------------+
| count(distinct types) |
+-----------------------+
|                  1306 |
+-----------------------+

So, I want to know which types of Type tables do not exist in SuperType with the following query but I got empty set instead of 4 types.

How can I fix this problem?

mysql> select distinct types from Types where not exists 
(select distinct type from SuperTypes,Types where SuperTypes.type=Types.types);

Empty set (0.00 sec)

Upvotes: 0

Views: 113

Answers (1)

gmiley
gmiley

Reputation: 6604

This is a situation where NOT IN may be better suited, try the following:

select distinct types 
from Types 
where types not in 
(
   select distinct type 
   from SuperTypes
)
;

Or you could do a join:

   select distinct t.types 
   from Types t
   left join SuperTypes st
   on st.type = t.types
   where st.type is null;

Also, the reason your query is not working is because your subquery is referencing its own column, if you change your query to the following, it should work just as well:

select distinct t1.types 
from Types t1
where not exists 
(
   select distinct st.type 
   from SuperTypes st, Types t2 
   where st.type = t1.types
);

The subquery needs to reference back out to the parent query to know what to match on. Which, you really do not need the join in the subquery at all:

select distinct t1.types 
from Types t1
where not exists 
(
   select distinct st.type 
   from SuperTypes st
   where st.type = t1.types
);

Upvotes: 2

Related Questions