SQL Server Performance Forum – Threads Archive

Query on Corelated Subqueries

Hello All, The below two queries work the same, as EXISTS simply
checks for a nonempty set (known fact).
I am trying to understand if there will be a
difference in performance between the two queries
(SELECT 1 or SELECT *). If so can any one pls advice
how it actually works in terms of I/O operations. SELECT T.title_id, title FROM titles T
WHERE NOT EXISTS
(SELECT 1
FROM sales S
WHERE T.title_id=S.title_id ) Works same as SELECT T.title_id, title FROM titles T
WHERE NOT EXISTS
(SELECT *
FROM sales S
WHERE T.title_id=S.title_id ) Thanks in advance. Thanks,
Shinoj R

---

No difference, you can check the execution plan

---

There are those who would even use this: WHERE EXISTS (SELECT NULL FROM ……………..) Not sure how that would work with NOT EXISTS, though …

---

As per pomela said, There is no difference between two queries, i have checked Execution plan for both queries..both plans are coming same.. Check with execution plans once again… SURYA

---

Hi, If you use "*" in select clause, the query engine has to pickup and produce the data from physical memory. So that obviously it will some I/O cost. But in the case of "1" there might not be any I/O cost. So it will be much faster than "*". -Saravanan..

---

If the no-of-rows are more, then we can identify the difference.. SURYA

---

In count(*) aggregate? There may be some parsing cost (but I don’t think it is the case with mssql server 2000+), but you’ll see no difference in execution time, because execution plan will be the same. Select * is completely different story.

---

Sorry, I mixed it with another thread. Anyway, the conclusion is the same. The execution plan will be the same. It could spend one or two more cpu cycles for parsing * (although I don’t think so) but you’ll not see difference in execution plan or execution time because of that, and when execution plan is cached it wouldn’t be any difference at all.

---

I agree. COUNT(*) will use the narrowest available index on the table. Since COUNT(1) must also use some physical on-disk structure to fulfill the request, what "faster" structure should this be than the narrowest available index? Every now and then you can see this mythos in online communities. But I haven’t seen any reliable repro script so far that proves this. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

This query was just an example..
Consider the case in which Sales.title_id is a nonclustered index. In this case I assume SELECT 1 will be faster than SELECT * as it can avoid going through the data pages. What say ?

---

SELECT 1 vs. SELECT * or did you mean
SELECT COUNT(1) vs. SELECT COUNT(*) ? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

It doesn’t metter again. Instead of asking for the third time the same question and recieving the same answer why don’t you just review the exeuction plan for both queries?

---

Sorry it was the other guy who offered the oposite opinion in the meantime. Anyway, pomela’s suggestion is valid one: take a look at execution plans, you will see no difference, run the query under the same circustances you’ll see no difference in execution time.

---

Hi all, You will get the same performance with both queries since: – EXITS just check for nonempty set and internally return either TRUE and NOT TRUE.
– it does not return columns so it does not matter if you use *, 1, or a list of column. ‘*’ is always used.
– If we look how it works EXITS with you example, we find that: 1. first query pass column value to the inner query.
2. inner query use the value to satisfy the inner query
3. inner query return a value back to the first query (TRUE or NOT TRUE) so as you can guess there are many I/O operation if the table has a lot rows. A better solution for your example would be use outer join since you would reduce the number of I/O operations.

---

]]>