SQL UNION and UNION ALL
UNION
The UNION command is used to select related information from two
tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.
Note: With UNION, only distinct values are selected.
SQL Statement 1
UNION
SQL Statement 2
|
Employees_Norway:
| E_ID |
E_Name |
| 01 |
Hansen, Ola |
| 02 |
Svendson, Tove |
| 03 |
Svendson, Stephen |
| 04 |
Pettersen, Kari |
Employees_USA:
| E_ID |
E_Name |
| 01 |
Turner, Sally |
| 02 |
Kent, Clark |
| 03 |
Svendson, Stephen |
| 04 |
Scott, Stephen |
Using the UNION Command
Example
List all different employee names in Norway and USA:
SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA |
Result
| E_Name |
| Hansen, Ola |
| Svendson, Tove |
| Svendson, Stephen |
| Pettersen, Kari |
| Turner, Sally |
| Kent, Clark |
| Scott, Stephen |
Note: This command cannot be used to list all employees in Norway and
USA. In the example above we have two employees with equal names, and only one
of them is listed. The UNION command only selects distinct values.
UNION ALL
The UNION ALL command is equal to the UNION command, except that
UNION ALL selects all values.
SQL Statement 1
UNION ALL
SQL Statement 2
|
Using the UNION ALL Command
Example
List all employees in Norway and USA:
SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA |
Result
| E_Name |
| Hansen, Ola |
| Svendson, Tove |
| Svendson, Stephen |
| Pettersen, Kari |
| Turner, Sally |
| Kent, Clark |
| Svendson, Stephen |
| Scott, Stephen |
Whether you're new to XML or already an advanced user, the user-friendly views
and powerful entry helpers, wizards, and debuggers in XMLSpy are designed to
meet your XML and Web services development needs from start to finish.
- XML editor
- Graphical XML Schema / DTD editors
- XSLT 1.0/2.0 editor, debugger, profiler
- XQuery editor, debugger, profiler
- Support for Office Open XML (OOXML)
- Graphical WSDL editor & SOAP debugger
- Java, C#, C++ code generation
- And much more!
Download a free 30-day trial today!
Click to see a demo!
Learn what’s new in v2008
|
HOME
Printer friendly page
