This discussion is archived
7 Replies Latest reply: Jan 8, 2013 10:49 AM by 6363 RSS

select 1

952526 Newbie
Currently Being Moderated
Hi, can anyexplain the following , regarding "select 1"

does this "select 1" means first column from TRACK_PRODUCT_DEF ?
or

TRACK_ID, PRODUCT_CODE, CREATE_TIME from TRACK_PRODUCT_DEF ?
Thanks
newbie



INSERT INTO TMP_VRZ_LATEST_SOC NOLOGGING (TRACK_ID, PRODUCT_CODE, CREATE_TIME)
SELECT TRACK_ID, PRODUCT_CODE, CREATE_TIME
FROM MISPNA.TNP_LATEST_SOC s
WHERE EXISTS (
SELECT 1 FROM TRACK_PRODUCT_DEF c
WHERE UPPER(S.PRODUCT_CODE) = UPPER(C.PRODUCT_CODE)
);
  • 1. Re: select 1
    sb92075 Guru
    Currently Being Moderated
    949523 wrote:
    Hi, can anyexplain the following , regarding "select 1"

    does this "select 1" means first column from TRACK_PRODUCT_DEF ?
    or

    TRACK_ID, PRODUCT_CODE, CREATE_TIME from TRACK_PRODUCT_DEF ?
    Thanks
    newbie



    INSERT INTO TMP_VRZ_LATEST_SOC NOLOGGING (TRACK_ID, PRODUCT_CODE, CREATE_TIME)
    SELECT TRACK_ID, PRODUCT_CODE, CREATE_TIME
    FROM MISPNA.TNP_LATEST_SOC s
    WHERE EXISTS (
    SELECT 1 FROM TRACK_PRODUCT_DEF c
    WHERE UPPER(S.PRODUCT_CODE) = UPPER(C.PRODUCT_CODE)
    );
    SELECT 1 returns 1
    SQL> select 1 from tt;
    
             1
    ----------
             1
             1
             1
             1
             1
             1
    
    6 rows selected.
    
    SQL> select 1 from dual;
    
             1
    ----------
             1
  • 2. Re: select 1
    952526 Newbie
    Currently Being Moderated
    so why use "select 1" here? any significance?
  • 3. Re: select 1
    852736 Journeyer
    Currently Being Moderated
    select 1 - 1 is nothing but the value, which is just like when you are selecting any column value from a table.
  • 4. Re: select 1
    sb92075 Guru
    Currently Being Moderated
    949523 wrote:
    so why use "select 1" here? any significance?
    Any value, including NULL, could have been returned since the EXISTS just care about the WHERE clause being evaluated to TRUE.
  • 5. Re: select 1
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    The select 1 is used in a subquery of an exists.
    Exists returns true if the subquerie returns any row. So the 1 is not used. Only if the subquery returns rows or not is important.

    Most of the time we use select * ... in a case like this.

    Regasrds,

    Peter
  • 6. Re: select 1
    852736 Journeyer
    Currently Being Moderated
    In that query it's used to check the existence of the record in the other table based on the join.
  • 7. Re: select 1
    6363 Guru
    Currently Being Moderated
    No, it can be anything. What is selected makes no difference, exists condition just checks if the select returned at least one row.

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:442029737684#5692820553867

    >
    I find select null to be semantically more meaningful. You are NOT selecting anything really -- so
    say that.
    >

    I tend to agree with that and select null in exists conditions.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points