UNION INTERSECT and EXCEPT (SQL)

Help Contents

Introduction

A Query Expression Body allows you to combine the result of one or more queries using UNION, INTERSECT, or EXCEPT set operators.

Syntax

query-expression-body ::=
  query-term
  | query-expression-body UNION [ ALL | DISTINCT ] query-term
  | query-expression-body EXCEPT [ ALL | DISTINCT ] query-term

query-term ::=
  query-primary
  | query-term INTERSECT [ ALL | DISTINCT ] query-primary

query-primary ::=
  select-statement
  | ( query-expression-body )

Remarks

The UNION, EXCEPT, or INTERSECT operators combine the results of two queries into a single result:

If the ALL keyword is used, the result includes duplicates from both tables. If the DISTINCT keyword is used, duplicates are removed from the result. If no keyword is used, DISTINCT is assumed and duplicates are removed from the result.

Two queries combined using a UNION, EXCEPT, or INTERSECT operator must have the same number of columns.

Examples

The UNION operator used to create a single result set containing all tags:

SELECT FullName,CAST( Value AS VarChar) "Value","Timestamp" FROM TagDataPointAnalog
UNION ALL
SELECT FullName,CAST( Value AS VarChar),"Timestamp" FROM TagDataPointDigital
UNION ALL
SELECT FullName,CAST( Value AS VarChar),"Timestamp" FROM TagDataPointWord
UNION ALL 
SELECT FullName,CAST( Value AS VarChar),"Timestamp" FROM TagDataPointDword

Further Information

Fernhill SCADA SQL

To learn more about the SQL features in Fernhill SCADA.

Glossary

For the meaning of terms used in Fernhill SCADA.