CIS4365: Database Applications
Fall, 2017

What other operators are there?

Here you have some operators used in SQL. The first four operators are SET operators. These are used to combine the results of a multiple queries into a single result.

UNION ALL

The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values. 

SELECT [Statement 1]

UNION ALL

SELECT [Statement 2] 

UNION

The purpose of the SQL UNION command is to combine the results of two queries together. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected (eliminates any duplicate rows from that result set).

SELECT [Statement] 1]

UNION

SELECT [Statement 2]  

MINUS

The MINUS operates on two SQL statements. It takes all the results from the first SQL statement, and then subtract 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.

SELECT [Statement1]

MINUS

SELECT [Statement2]

INTERSECT

INTERSECT also operates on two SQL statements, similar to UNION. The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both statements).

SELECT [Statement 1]

INTERSECT

SELECT [Statement 2]  

BETWEEN

The BETWEEN keyword allows for selecting a range of data between two values. The syntax for the BETWEEN clause is as follows:

SELECT "column_name"

FROM "table_name"

WHERE "column_name" BETWEEN 'value1' AND 'value2'

This will select all rows whose column has a value between 'value1' and 'value2'.

LIKE

You use the LIKE operator to compare a character, string, or CLOB value to a pattern. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match.

SELECT "column_name"

FROM "table_name"

WHERE "column_name" LIKE {PATTERN}

 
REVIEW QUESTIONS

1. What is the difference between UNION and UNION ALL?     

2. What is the purpose of the MINUS function?

3. Returns only those rows that are returned by each of two SELECT statements

    a)    UNION

b)    MINUS

c)     INTERSECT

d)    None of the above

 4. Allows you to do a search based on a pattern rather than specifying exactly what is desired or spell out a range. 

a)    LIKE

b)    INTERSECT

c)     BETWEEN

d)    UNION ALL

 5. _______ selects a range of data between two values

 See answers

 See related links and references