5 Replies Latest reply: Jan 10, 2006 3:41 AM by 475667 RSS

    Why to select NULL

    423405
      Why should null be selected in a query??

      ex : I have seen a query which is as follows

      Select NULL from ..........

      Why should even this be done

      Select '1' from .....

      A query can be where it can select the column names but why should the above formats be used...Is this used for performance or any specific purpose???

      TIA
      Shekar.
        • 1. Re: Why to select NULL
          472871
          As far as Select '1' from ............ query is concerned, that is mostly used to just test a where condition is successful or not in the query; since the column might not be indexed or the table is huge in size, only '1' is selected so as to just select whether a query returns any rows or not.....

          And regarding the query Select NULL FROM ............ I feel this should have been done only if we are using UNION or UNION ALL of more than 2 different set of data and for that if columns fall short in any query...to UNION or UNION ALL , that would be used.
          • 2. Re: Why to select NULL
            475667
            Are you using EXISTS in your statement?

            Bye, Aron
            • 3. Re: Why to select NULL
              423405
              Yes there is a exists in the statement
              • 4. Re: Why to select NULL
                Dmytro Dekhtyaryuk
                Hallo,
                read this link :

                http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions012.htm

                example from the manual
                SELECT department_id
                  FROM departments d
                  WHERE EXISTS
                  (SELECT * FROM employees e
                    WHERE d.department_id
                    = e.department_id);
                We must check, whether subquery returns at least one row.
                But it is not important, what we receive in SELECT- list of sub-query.
                That's why we can rewrite the subquery
                SELECT department_id
                  FROM departments d
                  WHERE EXISTS
                  (SELECT NULL FROM employees e
                    WHERE d.department_id
                    = e.department_id);
                Subquery returns as in previous example true or false.

                Regards
                Dmytro
                • 5. Re: Why to select NULL
                  475667
                  Using EXISTS it's normal that you have such statements.
                  SQL> create table dept(dept_id number, name varchar(30))
                  Table created

                  SQL> alter table dept add constraint pk_dept_id primary key (dept_id)
                  Table altered

                  SQL> insert into dept values(1, 'Financial service')
                  1 row inserted


                  SQL> insert into dept values(2, 'Administration')
                  1 row inserted


                  SQL> insert into dept values(3, 'HR')
                  1 row inserted


                  SQL> create table emp(name varchar2(20), dept_id number)
                  Table created

                  SQL> alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(d
                  Table altered

                  SQL> insert into emp values ('Bud Spencer', 2)
                  1 row inserted


                  SQL> insert into emp values ('Terence Hill', 2)
                  1 row inserted


                  SQL> insert into emp values ('Winona Ryder', 3)
                  1 row inserted
                  Executing this 2 statements you will have the same result:
                  SQL> select *
                  from emp
                  where exists (select 1 from dept where dept_id = 2 and dept_id = emp.dept_id)
                  NAME                 DEPT_ID
                  -------------------- -------
                  Bud Spencer                2
                  Terence Hill               2
                  2 rows selected


                  SQL> select *
                  from emp
                  where exists (select null from dept where dept_id = 2 and dept_id = emp.dept_id)
                  NAME                 DEPT_ID
                  -------------------- -------
                  Bud Spencer                2
                  Terence Hill               2
                  2 rows selected
                  EXIST checks if the sub-select return any rows. If it does, it return true otherwise false. NULL or 1 are only "symbolic" name (they are never used outside the sub-select).

                  HTH

                  Aron