exists/not exists select 1 vs select *

Hi, i understand select * has a worse performance than select 1 as it needs to expand the columns etc..

But is there any difference logically between those sql, and are there ever an instance where you must use select * instead of select 1.  

select * from table1 where not exists  (select 1 from table2)
vs
select * from table1 where not exists  (select * from table2)

thanks
LVL 1
gagaliyaAsked:
Who is Participating?
 
alpmoonConnect With a Mentor Commented:
When it is said "logically", I thought we are talking about the result set rather than query plan or physical performance.
0
 
alpmoonCommented:
I can't think of any case which make a difference between two queries. I am curious to hear if anyone argues it.
0
 
wilcoxonConnect With a Mentor Commented:
On most databases, the performance difference is very small (but it does exist).  I don't think there is ever a case where you need to use "select *" rather than "select 1".  At least, I've never run into one and I can't think of any.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
Supporting wilcoxon, it does make a difference, and sometimes that difference can be significant enough to care about.

If the subquery table in the example is very deep (many rows) and very wide (many and/or large columns) then the difference between "select 1" and "select *" can really add up.

In the case where the table width is 1952 bytes and there are 100M rows, then "select *" has to do I/O on 100M rows x 1952 bytes = 181.79Gb. Whereas if we do "select 1", the row width is now effectively 1 byte, which means the total I/Os are 100M rows x 1 byte = 95.36Mb, or roughly 0.0005 x the I/O of "select *". 181Gb vs 95Mb is a pretty big difference in I/O, and you would definitely see a difference in performance. Consider too the likely effects on data cache even if MRU replacement was used.

I am exaggerating for effect, it's seldom we'd see such a big difference, but anything on the inside of a join or within an expression should be optimised as far as possible because those differences do add up.

Even if you argue that the example above is going to table scan either way, consider the case where there are indexes. Without a WHERE clause, "select *" must read the full rows, but ASE is smart enough to use the narrowest NC index for "select 1" - effectively this is a special case of NC index "covering" (obviously we don't need to read the actual data rows when we don't care about any of the columns, thus every NC index covers "select 1").
0
 
Jan FranekCommented:
Hi Joe,

if I understand you correctly, this means, that the most effective solution would be:

select * from table1 where not exists  (select top 1 1 from table2)

This should need only 1 byte IO (1 row x 1 byte). Right ?
0
 
Jan FranekCommented:
> This should need only 1 byte IO (1 row x 1 byte). Right ?

I mean just for subquery.
0
 
Joe WoodhousePrincipal ConsultantCommented:
The "top" would normally require sorting which would likely slow things down. I know it doesn't look on first glance like "select 1" would need sorting but I am fairly sure "top" involves the idea of row order - unlike, say, the effects of "set rowcount 1" which stops processing after the result set has 1 row.
0
 
alpmoonCommented:
Physical performance difference is obvious. I thought that we are discussing whether there is a case they are different logically. I can't imagine a where clause which makes them logically different even though it may include a join clause between table1 and table2:

select * from table1 where not exists  (select 1 from table2 where ......)
vs
select * from table1 where not exists  (select * from table2 where ......)

I am still curious to hear if anyone argues it.
0
 
Joe WoodhousePrincipal ConsultantCommented:
If you're saying would those two most recent examples have a different query plan, for table scans no I don't believe so. NC index covering per my earlier comment could produce a different query plan for the inner query though.
0
 
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
Oh! My mistake. Yes, absolutely zero difference to overall result set.
0
 
gagaliyaAuthor Commented:
Yes i was mostly asking the logical difference in the resultset.

Thank you guys, it's very clear now.
0
All Courses

From novice to tech pro — start learning today.