Product Description

Mastering Oracle SQL, 2nd Edition fills the gap between the sometimes spotty vendor documentation, and other books on SQL that just don't explore the full depth of what is possible with Oracle-specific SQL. For those who want to harness the untapped (and often overlooked) power of Oracle SQL, this essential guide for putting Oracle SQL to work will prove invaluable.

Product Details

• Amazon Sales Rank: #89622 in Books
• Published on: 2004-06-22
• Original language: English
• Number of items: 1
• Binding: Paperback
• 494 pages

From the Publisher
Updated to cover Oracle 10g, this new edition of the highly regarded Mastering Oracle SQL has a stronger focus on practical, expert best-practices and on Oracle-specific SQL technique than any other book on the market. For those who want to harness the untapped (and often overlooked) power of Oracle SQL, this essential guide for putting Oracle SQL to work will prove invaluable.

About the Author
Sanjay Mishra is a certified Oracle database administrator with more than ten years of IT experience. He has been involved in the design, architecture, and implementation of many mission-critical and decision support databases. He has worked extensively in the areas of database architecture, database management, backup / recovery, performance tuning, Oracle Parallel Server, and parallel execution. He has a Bachelor of Science degree in Electrical Engineering, and a Master of Engineering degree in Systems Science and Automation. He is the coauthor of Oracle Parallel Processing and Oracle SQL Loader: The Definitive Guide (both published by O'Reilly & Associates). Presently, he works as a database architect at Dallas Based i2 Technologies, and can be reached at sanjay_mishra@i2.com.

Alan Beaulieu has been designing, building, and implementing custom database applications for over 13 years. He currently runs his own consulting company that specializes in designing Oracle databases and supporting services in the fields of Financial Services and Telecommunications. In building large databases for both OLTP and OLAP environments, Alan utilizes such Oracle features as Parallel Query, Partitioning, and Parallel Server. Alan has a Bachelor of Science degree in Operations Research from the Cornell University School of Engineering. He lives in Massachusetts with his wife and two daughters and can be reached at albeau_mosql@yahoo.com.

Excerpted from Mastering Oracle SQL by Sanjay Mishra, Alan Beaulieu. Copyright © 2004. Reprinted by permission. All rights reserved.
CHAPTER 7 - Set Operations

There are situations when we need to combine the results from two or more SELECT statements. SQL enables us to handle these requirements by using set operations. The result of each SELECT statement can be treated as a set, and SQL set operations can be applied on those sets to arrive at a final result. Oracle SQL supports the following four set operations:

• UNION ALL
• UNION
• MINUS
• INTERSECT

SQL statements containing these set operators are referred to as compound queries, and each SELECT statement in a compound query is referred to as a component query. Two SELECTs can be combined into a compound query by a set operation only if they satisfy the following two conditions:

• The result sets of both the queries must have the same number of columns.
• The data type of each column in the second result set must match the data type of its corresponding column in the first result set.

These conditions are also referred to as union compatibility conditions. The term union compatibility is used even though these conditions apply to other set operations as well. Set operations are often called vertical joins, because the result combines data from two or more SELECTS based on columns instead of rows. The generic syntax of a query involving a set operation is:

component_query
{UNION | UNION ALL | MINUS | INTERSECT}
component_query

The keywords UNION, UNION ALL, MINUS, and INTERSECT are set operators. You can have more than two component queries in a composite query; you will always use one less set operator than the number of component queries.

There is an exception to the second union compatibility condition. Two data types do not need to be the same if they are in the same data type group. By data type group, we mean the general categories such as numbers, strings, and datetimes. For example, it is ok to have a column in the first component query of data type CHAR, that corresponds to a VARCHAR2 column in the second component query (or vice versa). Oracle performs implicit type conversion in such a case.

However, Oracle will not perform implicit type conversion if corresponding columns in the component queries belong to different data type groups. For example, if a column in the first component query is of data type DATE, and the corresponding column in the second component query is of data type CHAR, Oracle will not perform implicit conversion, and you will get an error as a result of violation of data type compatibility. This is illustrated in the following example:

SELECT TO_DATE('12-OCT-03') FROM DUAL
UNION
SELECT '13-OCT-03' FROM DUAL;
SELECT TO_DATE('12-OCT-03') FROM DUAL
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

The following sections discuss syntax, examples, rules, and restrictions for the four
set operations.

Set Operators
The following list briefly describes the four set operations supported by Oracle SQL:

UNION ALL
Combines the results of two SELECT statements into one result set.

UNION
Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

MINUS
Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement. Duplicate rows are eliminated.

INTERSECT
Returns only those rows that are returned by each of two SELECT statements. Duplicate rows are eliminated.

Before moving on to the details on these set operators, let’s look at the following two queries, which we’ll use as component queries in our subsequent examples. The first query retrieves all the customers in region 5:

SELECT cust_nbr, name
FROM customer
WHERE region_id = 5;
CUST_NBR NAME
---------- ------------------------------
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries

The second query retrieves all the customers with the sales representative 'MARTIN':

SELECT c.cust_nbr, c.name
FROM customer c
WHERE c.cust_nbr IN (SELECT o.cust_nbr
FROM cust_order o, employee e
WHERE o.sales_emp_id = e.emp_id
AND e.lname = 'MARTIN');
CUST_NBR NAME
---------- ------------------------------
4 Flowtech Inc.
8 Zantech Inc.

If you look at the results returned by these two queries, you will notice that there is one common row (for Flowtech Inc.). The following sections discuss the effects of the various set operations between these two result sets.

UNION ALL
The UNION ALL operator merges the result sets of two component queries. This operation returns rows retrieved by either of the component queries, without eliminating duplicates. The following example illustrates the UNION ALL operation:

SELECT cust_nbr, name
FROM customer
WHERE region_id = 5
UNION ALL
SELECT c.cust_nbr, c.name
FROM customer c
WHERE c.cust_nbr IN (SELECT o.cust_nbr
FROM cust_order o, employee e
WHERE o.sales_emp_id = e.emp_id
AND e.lname = 'MARTIN');

CUST_NBR NAME
---------- ------------------------------
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries
4 Flowtech Inc.
8 Zantech Inc.
7 rows selected.

As you can see from the result set, there is one customer, which is retrieved by both the SELECTs, and therefore appears twice in the result set. The UNION ALL operator simply merges the output of its component queries, without caring about any duplicates in the final result set.

Customer Reviews

Good depth, assumes a lot
I bought this book with the aim of extending my own use of SQL, which I have used for 10 years or more. I especially wanted to make more use of Oracle enhancements since I first used SQL. At first I wondered if I would get anything from the book, since it started off with explaining simple select/insert/update statements, but it very quickly got more in-depth. I found the depth to be excellent, but be warned if you are new to SQL: the writers very quickly start using technical terms without explanation, so if you're not already at least marginally confident with SQL, find a basics book first. I will be returning to this book repeatedly, it opened my eyes to so many more features that I'm dying to get to use!

Useful for newcomers - of little use to Oracle professionals
My main gripe with this book is the insistance of the authors (and Oracle for that matter) to force the new ANSI join down your throat, it is used in every single join with no examples using the old syantax. On top of this, there is not one actual example of this super new syntax joining more than two tables together, in fact almost all the examples given avoid any serious complexity.

Where the book is useful is perhaps in the introduction of topics which you may be unfamiliar such as XML or if you are a relative newcomer to Oracle.

Learn the How not the What
This is an excellent addition to your SQL book library for the very simple reason it teaches you HOW to write SQL queries. Its not a SQL reference book in the sense it doesnt teach you what all the basic functions do, but it does teach you how to write quality SQL i.e. the concepts behinding writing SQL statements.