1

When i write:

if not exists (select * from sys_maxkey)

is better,or

if not exists (select id from sys_maxkey)

is better when the table sys_maxkey variable.

or it has no difference?I have found internet and analyse exists and in but not pay attention to this special point.

Thank you!

Dolphin
  • 29,069
  • 61
  • 260
  • 539

1 Answers1

1

I would suggest to use only 1 like this. This would be the best. As exists only checks whether there is any entry in your table.

if not exists (select 1 from sys_maxkey)

Although if you want to compare between the above two mentioned in your question then 

if not exists (select id from sys_maxkey)

would be better provided that id is the primary key. You dont have to check all the columns by using * just for checking whether there exists a value in your table.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • And if you tell me is it have effect about performance using * ?If the table is very very big?Yes i use the primary key to judge. – Dolphin Oct 20 '13 at 04:48
  • Yes definitely but only if your table is very very big. You definitely dont require to fetch all the column just for checking whether the table has an entry or not ;) – Rahul Tripathi Oct 20 '13 at 04:50
  • **Yes i use the primary key to judge**..then you definitely dont need to use **"*"** for checking if the entry in the table exists. – Rahul Tripathi Oct 20 '13 at 04:51
  • select 1 and select id ,which is better?Oh i know...... may be it has no difference....because the primary key is unique! – Dolphin Oct 20 '13 at 04:52
  • 1
    @Dolphin:- select 1 will still be a better choice as it will only check if there is an entry in database. But when you say select id from table then it would fetch all the id's from the table. So performance wise select 1 would be better :) Hope that makes it clear! – Rahul Tripathi Oct 20 '13 at 04:55