MySQL : Select all row of table depends on foreign key?

Question

I don't know what is actual question, but my problem is given below ::

I have a MySQL table table_name something like this:

+----+--------+-------+
| id | name   | f_key |
+----+--------+--------
| 1  | abcd   |   0   |
| 2  | efgh   |   0   |
| 3  | ijkl   |   0   |
| 4  | mnop   |   2   |
+----+--------+-------+

id is primary key & f_key is foreign key [reference to id in same table].

SELECT * FROM table_name WHERE id NOT IN (SELECT f_key FROM table_name WHERE f_key !=0);

is there any other good (minimum execution time) MySQL query to get output like this:

+----+--------+-------+
| id | name   | f_key |
+----+--------+--------
| 1  | abcd   |   0   |
| 3  | ijkl   |   0   |
| 4  | mnop   |   2   |
+----+--------+-------+

EDIT ::

Try to make query for medium size result of sub query, but the problem is :

• EXISTS executes at high speed against IN : when the subquery results is very large.

• IN gets ahead of EXISTS : when the subquery results is very small.

Answer 1

In Operator often has bad performance. I think you should try exists

    SELECT * FROM table_name a WHERE NOT Exists(SELECT f_key FROM table_name b WHERE b.f_key = a.id);

And if you can create index in f_key, this query will perform better

Answer 2

Use the normal idiom for Return row only if value doesn't exist but with a self-join:

SELECT a.*
FROM table_name AS a
LEFT JOIN table_name AS b ON a.id = b.f_key
WHERE b.id IS NULL

Answer 3

Here its is said, that the not in or an outer join would have a better performance, but there the not in or the not exists wins...

SELECT a.* FROM table_name a left outer join table_name b ON b.f_key = a.id
WHERE b.id is null