SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Unions

  1. #1
    SitePoint Evangelist Andrewaclt's Avatar
    Join Date
    Dec 2003
    Location
    Raleigh, NC
    Posts
    535
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unions

    Okay, I have neve understood unions, at all...I know basic sql, how to select/delete/insert/etc. but unions seem to really throw me, I looked at the syntax but bleh, that only confuses me more...

    Does anybody know of a good tutorial, or can explain it clearly, I know it joins two queries, but so what? What does it look like? What does the result look like?

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wouldn't really say that it "joins" 2 queries, I'm sure you know that a UNION is diff than a join, but still...when talking in syntactical expressions I wouldn't use the 2 interchangeably.

    A union combines or (or better yet...unions!! ) 2 queries together. You can union many queries together, no limit as far as I know. The architecture of the tables themselves does not have to be parallel, what is critical is that the architecture of each query or table view as defined in your SELECT statement is the same...as far as I know, the datatypes of each field that union together in the SELECT must be of the same datatype (I believe there are ways around this, not sure how accepted they are though).

    Let's take these 2 tables...
    Code:
    in both tables
    ---------------- 	
    ID	   //Int	
    LastName   //varchar(30)			
    FirstName  //varchar(30)		
    
    
    
    StoreA_Customers //table 1
    ID	Firstname	LastName
    -------------------------------------
    -------------------------------------
    1	Bill		Gates
    2	Anita		Hill
    3	Larry		Hull
    4	Tom		Garry
    5	Mandy		Smith
    
    StoreB_Customers //table 2
    ID	Firstname	LastName
    -------------------------------------
    -------------------------------------
    6	Curious		Goerge
    7	Clark		Kent
    8	Peter		Parker
    9	Little		Nemo
    10	Jay		Leno
    From this simple example, you could run this...
    Code:
    select	* from StoreA_Customers
    UNION
    select	* from StoreB_Customers
    and you'd get one single view showing...
    Code:
    ID	Firstname	LastName
    -------------------------------------
    1	Bill		Gates
    2	Anita		Hill
    3	Larry		Hull
    4	Tom		Garry
    5	Mandy		Smith
    6	Curious		Goerge
    7	Clark		Kent
    8	Peter		Parker
    9	Little		Nemo
    10	Jay		Leno
    You can add where/and/or filtering to the UNION just as in any other query...
    Code:
    select	* from StoreA_Customers
    UNION
    select	* from StoreB_Customers
    where	ID in (1, 2, 3)
    or	Firstname = 'Jay'  --gives RS below
    
    ID	Firstname	LastName
    --------------------------------
    1	Bill		Gates
    2	Anita		Hill
    3	Larry		Hull
    10	Jay		Leno
    If you tried to do any of the following, you'd get an error...each select must have similar architecture and the datatypes must be the same...
    Code:
    --1)
    select ID, lastname from StoreA_Customers
    UNION
    select Firstname from StoreB_Customers
    
    2)
    Select ID from StoreA_Customers
    UNION
    Select lastname from StoreB_Customers
    Is this enough ramble?? Any other questions?

  3. #3
    Non-Member
    Join Date
    Nov 2002
    Location
    Earth
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ^ what he said. UNIONs are simple. There are also INTERSECTs and EXCEPTs.

  4. #4
    SitePoint Evangelist Andrewaclt's Avatar
    Join Date
    Dec 2003
    Location
    Raleigh, NC
    Posts
    535
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very Very Clear, Thanks a lot.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    nice explanation, null, except for one wee error:
    Quote Originally Posted by null
    You can add where/and/or filtering to the UNION just as in any other query...
    Code:
    select	* from StoreA_Customers
    UNION
    select	* from StoreB_Customers
    where	ID in (1, 2, 3)
    or	Firstname = 'Jay'  --gives RS below
    
    ID	Firstname	LastName
    --------------------------------
    1	Bill		Gates
    2	Anita		Hill
    3	Larry		Hull
    10	Jay		Leno
    sorry, no, it doesn't

    what you coded will yield
    Code:
    1	Bill		Gates
    2	Anita		Hill
    3	Larry		Hull
    4	Tom		Garry
    5	Mandy		Smith
    10	Jay		Leno
    i hope you see why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, impossible!!

    j/k...yes I know why
    Here's a trivial fix...
    Code:
    Select	*
    from	(
    	 select * from StoreA_Customers
    	 UNION
    	 Select * from storeB_Customers
    	) as A
    where	ID in (1, 2, 3)
    or	firstname = 'Jay'


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
  •