A series of options are available for establishing a relationship between results from several results tables and for generating a new results table on the basis of this relationship.
You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.
You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview
The UNION statement enables you to create a union for the individual results tables from several SELECT statements. In the simplest case, you can link together two results tables that have been created from the same base table.
You can also use the UNION statement to combine results tables that have been created from different tables. However, in this case you have to make sure that the data types of the relevant ith output columns are comparable. These data types do not have to be identical, since the maximum length is used, where applicable.
SELECT zip customer_zip FROM hotel.customer WHERE zip > '90000'
SELECT zip hotel_zip FROM hotel.hotel WHERE zip> '90000'
Displaying the zip codes greater than 90000for each table
Result
CUSTOMER_ZIP |
HOTEL_ZIP |
|
90018 |
90804 |
|
97213 |
90029 |
|
95054 |
92262 |
|
90018 |
||
92714 |
SELECT zip FROM hotel.customer WHERE zip > '90000'
UNION
SELECT zip FROM hotel.hotel WHERE zip > '90000'
Displaying all the zip codes from the customer and hotel tables that are greater than 90000
Result
ZIP |
90018 |
90029 |
92262 |
90804 |
97213 |
92714 |
95054 |
zip codes that occur several times are displayed once only, since the database system implicitly executes a DISTINCT. If you want to display all zip codes by their frequency of occurrence, use UNION ALL.
SELECT hno hno_or_cno, name hotel_or_customer_name, zip FROM hotel.hotel WHERE zip < '30000'
UNION ALL
SELECT cno, name, zip FROM hotel.customer WHERE zip < '30000'
Displaying all the cities/places in which either a hotel is located or one of the customers lives
Result
HNO_OR_CNO |
HOTEL_OR_CUSTOMER_NAME |
ZIP |
3000 |
Porter |
10580 |
30 |
Regency |
20037 |
4300 |
TOOLware |
20019 |
4400 |
Jenkins |
20903 |
10 |
Congress |
20005 |
3400 |
Griffith |
20005 |
80 |
Midtown |
10019 |
20 |
Long Island |
11788 |
70 |
Empire State |
12203 |
40 |
Eighth Avenue |
10019 |
You can use INTERSECT to generate an intersection.
SELECT zip FROM hotel.customer WHERE zip < '30000'
INTERSECT
SELECT zip FROM hotel.hotel WHERE zip < '30000'
Displaying the zip codes that are used in both tables
Result
ZIP |
20005 |
If you do not additionally specify ALL, an implicit DISTINCT is also executed here.
You can use EXCEPT to deduct the results from one results table from those of another. The sequence of the SELECT statements is important here.
SELECT zipFROM hotel.hotel WHERE zip < '30000'
EXCEPT
SELECT zip FROM hotel.customer WHERE zip < '30000'
Displaying only those zip codes from the hotel table that do not exist in the customer table
Result
ZIP |
12203 |
10019 |
20037 |
11788 |
Before the EXCEPT set operation becomes effective, a DISTINCT is implicitly executed.
If you also want to take into consideration the number of rows found in the individual results tables, use <UNION|INTERSECT|EXCEPT> ALL in each case.
SQL Reference Manual, QUERY Expression (query_expression)
More examples for Data Query