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

jennifer zen 341 Reputation points
2020-12-10T20:44:28.677+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,484 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-12-11T05:49:59.157+00:00

    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

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2020-12-10T22:39:26.217+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Tom Cooper 8,461 Reputation points
    2020-12-10T22:46:35.117+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  3. jennifer zen 341 Reputation points
    2020-12-11T22:43:11.963+00:00

    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

    0 comments No comments