Database Reference
In-Depth Information
FROM INSTRUMENT I
START WITH I.NAME = 'Guitar'
CONNECT BY PRIOR I.INSTRUMENT_ID = I.SECTION_ID
ORDER BY 1,2;
4.2.9
Composite Queries
Composite queries use what are called set operators (UNION [ALL],
INTERSECT, MINUS) to concatenate (add together) the results of multi-
ple queries. Composite queries are not the same as joins. The following
query would simply concatenate the results of the two queries as a
UNION. The result would include all rows from both queries together in
the result regardless of any relationship between the two tables.
SELECT NAME, ARTIST_ID FROM ARTIST
UNION
SELECT TITLE, SONG_ID FROM SONG;
Now that we have examined query types, let's look at some special
aspects of queries.
4.3
Other Aspects of the SELECT Statement
Various other aspects of SELECT statements are important to remember:
The DUAL table is a dummy or temporary table used to execute
non-SQL-type commands with the SQL command interpreter.
Using functions allows use of a large amount of built-in (provided)
functionality or even custom-written functions.
Arithmetic is allowed in SQL using standard arithmetic operators.
The DISTINCT function allows retrieval of unique values from a
row set containing duplicate values.
Null values represent nothing. A space character and the value 0 are
not the same as NULL. A null value is never an unknown value but is
simply a value that has never been set.
Pseudocolumns are special columns in Oracle Database that are cov-
ered in later chapters.
