How does 'SELECT TOP 1 1 FROM SAMPLETABLE' works inside a if exists( )

Question

Hello experts,

(Sorry for the long post)

I have this query (below at the very end) to update a field-->

Step 3:'Closed_Status' in a table 'Students' (set it to 1 based on some conditions or default will be 0)

Step 1: So the query is taking other fields 'RollNumber','StdActivity',StartDate','EndDate' from the same table 'students' to a #temptable1

Step 2: Then taking only 'RollNumber' to another #temptable2 based on where condition as below;

SELECT RollNumber INTO #temptable2 FROM #temptable1

WHERE EXISTS
(
SELECT TOP 1 1 FROM Students ST

left outer join ProjectDetails PD
on ST.RollNumber = PD.RollNumber
and PD.Code like 'PD128..'

WHERE ST.StartDate>=#temptable1.StartDate
AND ST.EndDate<=#temptable1.EndDate
AND PD.CompletionStatus IS NULL
HAVING SUM(ST.StdActivity) >= 8)

I want to understand the inner part of this WHERE EXIST() which is --> SELECT TOP 1 1 FROM Students ST

I believe since you are using TOP 1 1...the result will be always '1' , doesn't matter what conditions you use for filtering the records right?
so in the below query, what's the use where clause part in the query(Does this even taken into consideration to return 1 or 0)???

Isn't it similar to this below-->

SELECT RollNumber INTO #temptable2 FROM #temptable1

WHERE EXISTS
(
SELECT TOP 1 1 FROM Students ST
)

Full Original Query

---

---------------Step 1:--------------------------------

SELECT
RollNumber
, StdActivity
, startdate
, Enddate
INTO #temptable1
FROM Students
WHERE D_No > ''
---------------Step 2:--------------------------------
SELECT RollNumber INTO #temptable2 FROM #temptable1

WHERE EXISTS
(
SELECT TOP 1 1
FROM Students ST

left outer join ProjectDetails PD
on ST.RollNumber = PD.RollNumber
and PD.Code like 'PD128..'

WHERE ST.StartDate>=#temptable1.StartDate
AND ST.EndDate<=#temptable1.EndDate
AND PD.CompletionStatus IS NULL
HAVING SUM(ST.StdActivity) >= 8)
---------------Step 3:--------------------------------
Update Students
Set [Closed_Status]=1
From Students
INNER JOIN #temptable2 ON ST.RollNumber= #temptable2.RollNumber

ERROR: All the students are having 'Closed_Status' as '1' which is not right

Thanks in advance
Jenni

Answer 1

Hi @jennifer zen ,

For this type of problem we recommend that you post CREATE TABLE statements for your tables(students,ProjectDetails) together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

SELECT TOP 1 Means Selecting the very 1st record in the result set.

SELECT 1 Means return 1 as the result set.

SELECT TOP 1 1 FROM [SomeTable] WHERE <SomeCondition> Means if the condition is true and any rows are returned from the select, only return top 1 row and only return integer 1 for the row (no data just the integer 1 is returned).

In addition, there is no difference in efficiency between (NOT) EXISTS (SELECT 1 ...) and (NOT) EXISTS (SELECT * ...).

You could try with below update at step2 and check whether it is working to you.

---------------Step 2:--------------------------------  
SELECT RollNumber INTO #temptable2 FROM #temptable1  
WHERE EXISTS  
(  
SELECT TOP 1 1  --Consider whether to remove 'top 1'  
FROM Students ST  
left outer join ProjectDetails PD  
on ST.RollNumber = PD.RollNumber  
and PD.Code like 'PD128..'  
WHERE ST.StartDate>=#temptable1.StartDate  
AND ST.EndDate<=#temptable1.EndDate  
AND PD.CompletionStatus IS NULL  
AND #temptable1.RollNumber=ST.RollNumber --Add this condition here  
HAVING SUM(ST.StdActivity) >= 8)  

Or you could update the whole statement like below:

Update a  
Set [Closed_Status]=1  
From Students a  
where a.RollNumber in (  
select st.RollNumber   
From Students ST  
left outer join ProjectDetails PD  
on ST.RollNumber = PD.RollNumber  
and PD.Code like 'PD128..'  
WHERE ST.D_No > ''  
AND PD.CompletionStatus IS NULL  
group by st.RollNumber  
HAVING SUM(ST.StdActivity) >= 8)  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

TOP 1 1 means nothing in this context; it only serves to make things cryptic. In an EXISTS subquery, it does not really matter what you put after SELECT, and people normally put an *. (But given the GROUP and HAVING SQL Server may object, so you have to put a 1 there - but without the TOP 1.

As for why you get the wrong result, we can't tell. We need to see your table definitions and sample data, preferably as CREATE TABLE + INSERT statements.

Answer 3

As YithakKhabinsky-0887 noted, supplying sample tables and data and the desired result from that sample data will get you faster, better answers.

That said, WHERE EXISTS(...) returns true if the query inside the parenthesis returns at least one row. It doesn't make any difference whether it returns 1 row or 1,000,000 rows. Because of that SELECT 1 1 ... and SELECT 1 ... and SELECT * ... all do exactly the same thing and are equally efficient.

Without the sample data and desired result, it's hard to know for sure what change(s) you need. But my guess is your problem is that you are not tying #temptable1.RollNumber to ST.RollNumber inside your WHERE EXISTS(...). So try

WHERE EXISTS
(
SELECT TOP 1 1 FROM Students ST

left outer join ProjectDetails PD
on ST.RollNumber = PD.RollNumber
and PD.Code like 'PD128..'
and #temptable1.RollNumber = ST.RollNumber  /* this is the line I added */

WHERE ST.StartDate>=#temptable1.StartDate
AND ST.EndDate<=#temptable1.EndDate
AND PD.CompletionStatus IS NULL
HAVING SUM(ST.StdActivity) >= 8)

Tom

Answer 4

Hello everyone,

Oopsie my bad..I forgot to upload the pic I took of my table's data and 'create' script.
But @MelissaMa-MSFT got that right and explained the difference between them quite well :)
Thanks for that and as you suggested, changing the update script fixed the issue.

Again, appreciate your help from all of guys :)

Thanks