SQL: querying primary key coloumn for condition to "where col is NULL"

Question

How sql engine process a query when some one hit the db with a query like

select * from table where primary_key_col is NULL

since primary key coloumn can't have null, will it straight away return empty rows or it tries to hit the index to search

Answer 1

primary_key_col=NULL cant be used since you can not compare null using = operator, you need to use is null

select * from table where primary_key_col is null

http://dev.mysql.com/doc/refman/5.7/en/working-with-null.html

UPDATE : I misquoted the question

since primary key coloumn can't have null, will it straight away return empty rows or it tries to hit the index to search

This could ne explained with the following explain query. I have a table users and idusers is the PK auto-incremented (mysql 5.6)

Now lets run an explain and see

mysql> explain select * from users where idusers is null ;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.01 sec)

Where you can see that it gives you impossible where http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

MySQL has read all const (and system) tables and notice that the WHERE clause is always false.

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#jointype_const

So your index table may not be even scanned by the optimizer after it fails on the first place.