Professional Web Applications Themes

About 'Union' - Microsoft SQL / MS SQL Server

Hi, All, When I use the 'Union' to get result from two tables. which table's info will be shown first? Is table1 or table2? (without order by) From BOL, Table1 and Table2. Table1 Table2 ColumnA ColumnB ColumnC ColumnD char(4) int char(4) int ------- --- ------- --- abc 1 ghi 3 def 2 jkl 4 ghi 3 mno 5 This query creates a UNION between the two tables: SELECT * FROM Table1 UNION SELECT * FROM Table2 Here is the result set: ------ --- abc 1 def 2 ghi 3 jkl 4 mno 5 but I get the same result, different ...

  1. #1

    Default About 'Union'

    Hi, All,
    When I use the 'Union' to get result from two tables.
    which table's info will be shown first? Is table1 or
    table2? (without order by)

    From BOL, Table1 and Table2.
    Table1 Table2
    ColumnA ColumnB ColumnC ColumnD
    char(4) int char(4) int
    ------- --- ------- ---
    abc 1 ghi 3
    def 2 jkl 4
    ghi 3 mno 5

    This query creates a UNION between the two tables:
    SELECT * FROM Table1
    UNION
    SELECT * FROM Table2

    Here is the result set:
    ------ ---
    abc 1
    def 2
    ghi 3
    jkl 4
    mno 5

    but I get the same result, different order.

    ColumnA ColumnB
    ------ ---
    ghi 3
    jkl 4
    mno 5
    abc 1
    def 2

    So, which table's info will be shown first(without order
    by), table1 or table2? If I want table1 always show first,
    what can I do?

    Thanks!

    Have a good one.

    Hawk

    Hawk Guest

  2. #2

    Default Re: About 'Union'

    Hawk,

    UNION Specifies that multiple result sets are to be combined and returned
    as a single result set.

    From that definition, there is no way that one table or another can be
    guaranteed to appear first. Cardinal SQL Server rule: If order matters, use
    ORDER BY.

    Now, if you want Table 1 to appear first, you could add a column to your
    query as such:

    SELECT 1 AS TableNum, * FROM Table1
    UNION
    SELECT 2 AS TableNum, * FROM Table2
    ORDER BY TableNum

    Russell Fields


    "Hawk" <abchawkhotmail.com> wrote in message
    news:041001c340d0$df44d030$a101280aphx.gbl...
    > Hi, All,
    > When I use the 'Union' to get result from two tables.
    > which table's info will be shown first? Is table1 or
    > table2? (without order by)
    >
    > From BOL, Table1 and Table2.
    > Table1 Table2
    > ColumnA ColumnB ColumnC ColumnD
    > char(4) int char(4) int
    > ------- --- ------- ---
    > abc 1 ghi 3
    > def 2 jkl 4
    > ghi 3 mno 5
    >
    > This query creates a UNION between the two tables:
    > SELECT * FROM Table1
    > UNION
    > SELECT * FROM Table2
    >
    > Here is the result set:
    > ------ ---
    > abc 1
    > def 2
    > ghi 3
    > jkl 4
    > mno 5
    >
    > but I get the same result, different order.
    >
    > ColumnA ColumnB
    > ------ ---
    > ghi 3
    > jkl 4
    > mno 5
    > abc 1
    > def 2
    >
    > So, which table's info will be shown first(without order
    > by), table1 or table2? If I want table1 always show first,
    > what can I do?
    >
    > Thanks!
    >
    > Have a good one.
    >
    > Hawk
    >

    Russell Fields Guest

  3. #3

    Default Re: About 'Union'

    Furthermore (having forgetton(?) to use UNION ALL instead of UNION),
    multiple rows from either or both tables may get "combined" into a single
    row.

    Bye,
    Delbert Glass


    Delbert Glass Guest

Similar Threads

  1. Count from a union
    By Derek Fountain in forum MySQL
    Replies: 2
    Last Post: August 18th, 09:15 AM
  2. Union Queries In ASP?
    By MDW in forum ASP Database
    Replies: 2
    Last Post: April 25th, 10:23 PM
  3. Php - union
    By Ryan A in forum PHP Development
    Replies: 0
    Last Post: August 8th, 01:44 AM
  4. UNION ALL views
    By Fan Ruo Xin in forum IBM DB2
    Replies: 4
    Last Post: August 6th, 01:35 PM
  5. need help with mysql union
    By r-militante@northwestern.edu in forum PHP Development
    Replies: 1
    Last Post: July 21st, 06:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139