7 Replies Latest reply: Jan 8, 2013 12:49 PM by 6363 RSS

    select 1

    952526
      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
          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
            so why use "select 1" here? any significance?
            • 3. Re: select 1
              852736
              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
                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
                  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
                    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
                      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.