8 Replies Latest reply: Dec 12, 2012 8:38 AM by CP RSS

    Query Output..

    CP
      SELECT *
      FROM EMPLOYEE
      WHERE EMP_ID IN (SELECT EMP_ID FROM DEPT ORDER BY DEPT_ID)
      Will my output of this query will be arranged as ordered inside my sub query?
        • 1. Re: Query Output..
          Toon_Koppelaars-Oracle
          CP wrote:
          SELECT *
          FROM EMPLOYEE
          WHERE EMP_ID IN (SELECT EMP_ID FROM DEPT ORDER BY DEPT_ID)
          I doubt there is an EMP_ID column inside DEPT...
          Do you really mean this query (using explicit table aliases now):
          SELECT e.*
          FROM EMPLOYEE e
          WHERE e.EMP_ID IN (SELECT d.EMP_ID FROM DEPT d ORDER BY d.DEPT_ID)
          Or are you querying this:
          SELECT e.*
          FROM EMPLOYEE e
          WHERE e.EMP_ID IN (SELECT e.EMP_ID FROM DEPT d ORDER BY d.DEPT_ID)
          Which is quite a non-sense-icle query.
          • 2. Re: Query Output..
            _Karthick_
            CP wrote:
            SELECT *
            FROM EMPLOYEE
            WHERE EMP_ID IN (SELECT EMP_ID FROM DEPT ORDER BY DEPT_ID)
            Will my output of this query will be arranged as ordered inside my sub query?
            No You need to write your query like this

            select e.*
              from employee e
              join dept d
                on e.emp_id = d.emp_id
            order by d.dept_id
            But emp_id column in DEPT? Does not look correct to me.
            • 3. Re: Query Output..
              DavidPaul
              Hi,

              I have never seen EMP table and DEPT table are designed like yours. Normally, EMP_ID is the primary key of EMP table, and DEPT_ID is the foreign key of EMP table.
              It seems the tables are designed incorrectly.

              Thanks,
              David
              • 4. Re: Query Output..
                CP
                >
                Toon.Koppelaars2 wrote:
                Do you really mean this query (using explicit table aliases now):

                SELECT e.*
                FROM EMPLOYEE e
                WHERE e.EMP_ID IN (SELECT d.EMP_ID FROM DEPT d ORDER BY d.DEPT_ID)
                >

                I meant the above query.

                >
                Toon.Koppelaars2 wrote:
                I doubt there is an EMP_ID column inside DEPT

                Karthick_Arp wrote:
                But emp_id column in DEPT? Does not look correct to me.

                David Paul wrote:
                I have never seen EMP table and DEPT table are designed like yours
                >

                Apologize for wrong example :). These are not my actual table.

                >
                Karthick_Arp wrote:

                No You need to write your query like this

                select e.*
                from employee e
                join dept d
                on e.emp_id = d.emp_id
                order by d.dept_id

                >

                I dont want to write the query with a Join. I am ordering EMP_ID in subquery and querying those EMP_ID in EMP table. Will my output be in the same order as Subquery.
                • 5. Re: Query Output..
                  sb92075
                  CP wrote:
                  >
                  Toon.Koppelaars2 wrote:
                  Do you really mean this query (using explicit table aliases now):

                  SELECT e.*
                  FROM EMPLOYEE e
                  WHERE e.EMP_ID IN (SELECT d.EMP_ID FROM DEPT d ORDER BY d.DEPT_ID)
                  >

                  I meant the above query.

                  >
                  Toon.Koppelaars2 wrote:
                  I doubt there is an EMP_ID column inside DEPT

                  Karthick_Arp wrote:
                  But emp_id column in DEPT? Does not look correct to me.

                  David Paul wrote:
                  I have never seen EMP table and DEPT table are designed like yours
                  >

                  Apologize for wrong example :). These are not my actual table.

                  >
                  Karthick_Arp wrote:

                  No You need to write your query like this

                  select e.*
                  from employee e
                  join dept d
                  on e.emp_id = d.emp_id
                  order by d.dept_id

                  >

                  I dont want to write the query with a Join. I am ordering EMP_ID in subquery and querying those EMP_ID in EMP table. Will my output be in the same order as Subquery.
                  if you require/desire results to be in a specific order, then include ORDER BY clause as part of SELECT
                  • 6. Re: Query Output..
                    6363
                    They might be, they might not. Do you want them in order? If yes, put order by at the end of the main query.
                    • 7. Re: Query Output..
                      Keith Jamieson
                      The correct answer is that it is not guaranteed to be returned in the same order as the order by in the subquery.

                      So you need to put the order by on the outer query.

                      The order by in the subquery is unnecessary and a waste of resource.
                      • 8. Re: Query Output..
                        CP
                        That answers my quetion.

                        Then this query will also not work. Write?
                        SELECT e.emp_id,
                               d.dept_id
                          FROM employee e,
                               dept     d
                         WHERE e.dept_id = d.dept_id
                           AND e.emp_id IN (SELECT emp_id
                                              FROM employee
                                             START WITH mgr_id IS NULL
                                            CONNECT BY PRIOR emp_id = mgr_id)
                        Connect By Query with Joins is not working properly.