FANDOM


What are Relational Set Operators? Edit

There are three primary SQL commands involved when implementing a Union, Intersection and difference relational operators. As you may know, SQL data manipulation commands are set-oriented which are involved in operating over entire sets of rows and columns in tables at once. The Union, Intersect, and Minus statements make sure these operations occur. The Union, Intersect, and Minus are the names of the SQL statements implemented in Oracle. Union, Intersect and Minus only work properly if relations are Union-compatible, which is based off the names of the relation attributes that must be the same and their data types must be alike. Being compatible does not mean the data types have to be exactly the same. For example, both data types can be used to store numeric values such as NUMBER and SMALLINT as well as character (string) values such as VARCHAR and CHAR.

Commonly Used SQL Relational Operators Edit

The following are the most common SQL commands used to implement the Union.


  • Union
  • Intersect
  • Minus


UNION Edit

A union can be used to bring together lists of data in which for example you don't want duplicates. The Union statement combines rows from two or more queries without including duplicate rows. In a way a Union may also be compared to a JOIN in that they are both used to related information from multiple tables. Some issues with Union's is that all corresponding columns need to be of the same data type and only distinct values are selected.

Syntax: query UNION query

The Union statement combines the output of two Select queries, in which these Select statements must be union-compatible. They must return values with the same attributes and data types. The following is a simple example of what a Union would look like:

SELECT       CUS_LNAME, CUS_FNAME, CUS_PHONE
FROM          CUSTOMER
UNION
SELECT       CUS_LNAME, CUS_FNAME, CUS_PHONE
FROM          CUSTOMER_2;


INTERSECT Edit

An intersect is good for pointing out what specific values are being duplicated. The Intersect command acts as if it is an AND operator (Value is only selected if in both statements). The Intersect can also generate additional useful information such as displaying customers in a specific zip-code.

Syntax: query INTERSECT query

A good example of finding a duplicate value would be:

SELECT       CUS_LNAME, CUS_FNAME, CUS_PHONE
FROM          CUSTOMER
INTERSECT
SELECT       CUS_LNAME, CUS_FNAME, CUS_PHONE FROM          CUSTOMER_2;

MINUS Edit

The Minus statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not the second. Basically it takes all the results from the first SQL statement, and then subtracts out the ones that are present in the second SQL statement to get the final answer. If the second SQL statement includes results not present in the first SQL statement, such results are ignored.

Syntax: query MINUS query

A good example of combining the rows and only seeing the first row:

SELECT       CUS_LNAME, CUS_FNAME, CUS_PHONE
FROM          CUSTOMER_2
MINUS
SELECT       CUS_LNAME, CUS_FNAME, CUS_PHONE

FROM           CUSTOMER;


References Edit

http://www.1keydata.com/sql/sqlunion.html
http://www.w3schools.com/sql/sql_union.asp

Rob, Peter; Coronel, Carlos; Database Systems: Design, Impllementation and Management; Eighth Edition; Pages 298-303

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.