Progress
SQL-92
Guide and Reference


Set Operator

This is the syntax for the set_operator:

SYNTAX
{ UNION [ ALL ] | INTERSECT | MINUS } 

UNION [ ALL ]

Appends the result table from one query expression to the result table from another.

The two query expressions must have the same number of columns in their result tables, and those columns must have the same or compatible data types.

The final result table contains the rows from the second query expression appended to the rows from the first. By default, the result table does not contain any duplicate rows from the second query expression. Specify UNION ALL to include duplicate rows in the result table.

The two query expressions must have the same number of columns in their result tables, and those columns must have the same or compatible data types.

INTERSECT

Limits rows in the final result table to those that exist in the result tables from both query expressions.

The two query expressions must have the same number of columns in their result tables, and those columns must have the same or compatible data types:

MINUS

Limits rows in the final result table to those that exist in the result table from the first query expression minus those that exist in the second. In other words, the MINUS operator returns rows that exist in the result table from the first query expression but that do not exist in the second.

The two query expressions must have the same number of columns in their result tables, and those columns must have the same or compatible data types.

EXAMPLES

The following example specifies all columns in the customer table:

SELECT * FROM customer; 

The table_name.* syntax is useful when the select list refers to columns in multiple tables and you want to specify all the columns in one of those tables:

SELECT customer.* FROM customer; 

SELECT Customer.CustNum, Customer.Name, Invoice.*
     FROM Customer, Invoice ; 

You can qualify a column name with the name of the table it belongs to. The following examples illustrate column names qualified by table name:

select customer.customer_id from customer ; 

The following example illustrates specifying a new column heading for an associated column. Enclose the new title in single or double quotation marks if it contains a space or other special characters:

-- Illustrate optional ’column_title’ syntax
select
        FirstName as ’First Name’,
        LastName as ’Last Name’, 
        state as ’New England State’ 
from employee
        where state = ’NH’ or state = ’ME’ or state = ’MA’
        or state = ’VT’ or state = ’CT’ or state = ’RI’;
 
First Name       Last Name        New England State  
---------------- ---------------- ------------------  
Justine          Smith            MA 
Andy             Davis            MA 
Marcy            Adams            MA 
Larry            Dawsen           MA 
John             Burton           NH 
Mark             Hall             NH 
Stacey           Smith            MA 
Scott            Abbott           MA 
Meredith         White            NH 
Heather          White            NH 

You must qualify a column name if it occurs in more than one table specified in the FROM clause:

-- Table name qualifier required
-- Customer table has city and state columns
-- Billto table has city and state columns
 
select
        Customer.custnum,
        Customer.city as "Customer City",
        Customer.State as ’Customer State’,
        Billto.City as "Bill City",
        Billto.State as ’Bill State’
from Customer, Billto
        where Customer.City = ’Clinton’;
 
 CustNum Customer City    Customer State   Bill City        Bill State  
-------- ---------------- ---------------- -------------------------------      
1272     Clinton          MS               Montgomery         AL               
1272     Clinton          MS               Atlanta            GA  
1421     Clinton          SC               Montgomery         AL  
1421     Clinton          SC               Atlanta            GA  
1489     Clinton          OK               Montgomery         AL  
1489     Clinton          OK               Atlanta            GA  

The following example selects only the customers whose name is ’SMITH’:

SELECT *
     FROM customer
     WHERE name = ’SMITH’ ; 

The following example finds all customers whose city is the same as the customer ’SMITH’.

SELECT *
     FROM customer
     WHERE city IN (
          SELECT city
          FROM customer
          WHERE name = ’SMITH’) ; 

The following example retrieves the customer and order information for customers with orders:

SELECT Customer.CustNum Customer.Name Orders.OrderNum Orders.OrderDate 
     FROM Customer, Orders 
     WHERE Customer.CustNum = Orders.CustNum ; 

The HAVING clause in the following example compares the value of an aggregate function
( COUNT (*) ) to a constant ( 10 ). The query returns the customer number and number of orders for all customers who had more than 10 orders before March 31st, 1999.

SELECT cust_no, count(*)
     FROM orders
     WHERE order_date < TO_DATE (’3/31/1999’)
     GROUP BY cust_no
     HAVING COUNT (*) > 10 ; 

The following examples show merging a list of customers and suppliers without and with duplicate entries:

-- Produce a merged list of customers and suppliers
SELECT name, street, state, zip
     FROM customer
     UNION
     SELECT name, street, state, zip
     FROM supplier ;
 
-- Produce a list of customers and suppliers
-- with duplicate entries for those customers who are also suppliers
SELECT name, street, state, zip
     FROM customer
     UNION ALL
     SELECT name, street, state, zip
     FROM supplier ; 

The following example illustrates the INTERSECT option:

-- Produce a list of customers who are also suppliers

SELECT name, street, state, zip FROM customer
     INTERSECT
SELECT name, street, state, zip FROM supplier ; 

The following example illustrates the MINUS option:

-- Produce a list of suppliers who are not customers
 
SELECT name, street, state, zip FROM supplier ;
     MINUS
SELECT name, street, state, zip FROM customer; 

AUTHORIZATION
SQL COMPLIANCE

SQL-92; the MINUS operator is equivalent to the SQL-92 EXCEPT operator

ENVIRONMENT

Embedded SQL, interactive SQL, ODBC applications

RELATED STATEMENTS

CREATE TABLE Statement, CREATE VIEW Statement, INSERT Statement, "Search Conditions," SELECT Statement, UPDATE Statement


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095