To visit my site

HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#


To contact us

© 2002 - 2015 All Rights Reserved Total Application Works



  • Tell a friend about this site (copy and paste the following HTML page into a file (e.g., using Notepad).)
  • <html>
    <head>
    <title>Example of a link </title>
    </head>
    <body>
    <a href="http://sumtotalz.com/TotalAppsWorks/SQL/MY_SQL_UNION.html">
    Lists and their Tags </a>
    </table>
    </body>
    </html>



SQL - UNION Keyword

Objectives

The objective of the lesson is to describe the SQL UNION command and present examples that show how to use it.

Introduction

What is a UNION query? The dictionary defines a union as "an act or instance of uniting or joining two or more things into one". The SQL UNION query allows the user to combine the row sets from different tables into a single result set. For example, consider the following tables.

Publishers
PublisherID
PublisherName
<------------>
Titles
ISBN
Title
PublisherID
EditionNumber
YearPublished
Description

<---+ 
   |
   |
      +-->
AuthorISBN
ISBN
AuthorID

 
    +->
  |
  |
<--+ 
Authors
AuthorID
FirstName
LastName
BirthDate
Figure 1: Books Database - tables


The tables in Figure 1 depict relationships. These relationships are also known as Entity-Relationships and Figure 1 can be considered an Entity-Relationship Diagram. What are relationships? The term "relationships" usually refers to the relationship that exists among the primary and foreign keys in the tables. When the tables of a relational database are designed, these relationships must be defined because they determine which columns are or are not primary or foreign keys. You may have heard of an Entity-Relationship Diagram, which is a graphical view of tables in a database schema, with lines connecting related columns across tables.

In this tutorial, we will explore combining the rows from two tables into one result set. The SQL commands we will be exploring will resemble the following statement.

 
SQL Statement 1
UNION
SQL Statement 2
 
It must be noted that each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types. In addition, you should note that duplicate rows are eliminated from the result set.

UNION Keyword

So far we have used the SQL SELECT command to retrieve data from a database. Once data has been retrieved, it can be displayed or edited within an application program. Let's consider some examples where the Select command is used along with the UNION command. What happens when the following SQL command is issued?

 
Example 1


SELECT FirstName, LastName AS Name FROM Authors
UNION
SELECT PublisherName AS Name FROM Publishers
 
As a result of the above query, we get the following SQL exception:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. Based on the above error, we modify the query to:
 
Example 2


"SELECT Authors.FirstName,  Authors.LastName AS Name FROM Authors"  +
" UNION " +
"SELECT Publishers.PublisherName AS Name FROM Publishers"
 
As a result of the above query, we get the following SQL exception:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. What's going on? Let's make one more change. Let's issue the following query.
 
Example 3


"SELECT Authors.LastName AS Name FROM Authors"  +
" UNION " +
"SELECT Publishers.PublisherName AS Name FROM Publishers"
 
As a result of the above query, we get the following output.
Name
Kathy Sierra and Bert Bates
Joshua Bloch
Brian Goetz, Tim Peierls, Joshua Bloch, and Joseph Bowbeer
Cay S. Horstmann and Gary Cornell
Ian Darwin
Andrew Davison
(Harvey & Paul) Deitel & Associates
Bruce Eckel
David Flanagan
Mark D. Hansen
Jonathan Knudsen
David J. Barnes and Michael Kolling
Rogers Cadenhead and Laura Lemay
Daniel Liang
Joel Murach, Andrea Steelman, and Doug Lowe
Herbert Schildt
Maurice Naftalin and Philip Wadler
Y. Daniel Liang and Hong Zhang
Addison-Wesley Professional
McGraw-Hill Osborne Media
O'Reilly Media, Inc.;
Prentice Hall
Prentice Hall PTR
Note: UNION queries require that the same number of fields be returned from each SELECT statement and that corresponding fields in each SELECT have compatible data types. However, you can use number and text fields in the same field position.

Union ALL Keyword

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values that meet the criteria specified in Statement 1 and Statement 1, and it does not eliminate duplicates. As a result of the above query, we get the following output.
Name
Kathy Sierra and Bert Bates
Joshua Bloch
Brian Goetz, Tim Peierls, Joshua Bloch, and Joseph Bowbeer
Cay S. Horstmann and Gary Cornell
Ian Darwin
Andrew Davison
(Harvey & Paul) Deitel & Associates
Deitel
Deitel
Bruce Eckel
Bruce Eckel
David Flanagan
Mark D. Hansen
Jonathan Knudsen
David J. Barnes and Michael Kolling
Rogers Cadenhead and Laura Lemay
Rogers Cadenhead and Laura Lemay
Daniel Liang
Daniel Liang
Joel Murach, Andrea Steelman, and Doug Lowe
Herbert Schildt
Maurice Naftalin and Philip Wadler
Y. Daniel Liang and Hong Zhang
Addison-Wesley Professional
McGraw-Hill Osborne Media
O'Reilly Media, Inc.;
Prentice Hall
Prentice Hall
Prentice Hall
Prentice Hall PTR
Prentice Hall PTR
Prentice Hall PTR



Summary

In this tutorial, we've explored the SQL UNION and UNION ALL commands. The SQL UNION query allows the user to combine the row sets from different tables into a single result set. This result set does not contain any duplicate rows. The SQL UNION ALL does produce duplicate rows.







result set

    A table of data representing a database result set, which is usually generated by executing a statement that queries the database.














[
HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#

© 2002 - 2015 All Rights Reserved Total Application Works