1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Surprising!

Discussion in 'SQL PL/SQL' started by jagadekara, Jul 30, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I am just surprised with this...

    select deptno from scott.emp order by deptno;

    DEPTNO
    --------
    10
    10
    10
    20
    20
    20
    20
    20
    30
    30
    30
    30
    30
    30

    14 rows....

    select distinct deptno from scott.emp order by deptno;

    DEPTNO
    --------
    10
    20
    30

    3 rows....

    select deptno from scott.emp
    union
    select 1 from dual;

    DEPTNO
    --------
    1
    10
    20
    30

    4 rows....

    select deptno from scott.emp
    union
    select deptno from scott.emp

    DEPTNO
    --------
    10
    20
    30

    3 rows....

    I know that Union will restrict duplicate values, but I never thought like this...
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    HI Jagadekar,

    I don't see anything strange in the output of these queries: the UNION operator always returns distinct rows.
    Maybe you're mixing it with UNION ALL which returns all rows including duplicates.

    As for:
    "1" is also returned as it follows the rule and syntax of UNION, i.e., same number of columns and same type (1 is number, and deptno is number or at least there's no issue in the implicit conversion of both columns).
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks All