Mastering Oracle SQL and over 2 million other books are available for Amazon Kindle . Learn more

Buy New

or
Sign in to turn on 1-Click ordering.
Buy Used
Used - Good See details
Price: £11.24

or
 
   
Trade in Yours
For a £4.25 Gift Card
Trade in
More Buying Choices
Have one to sell? Sell yours here
Start reading Mastering Oracle SQL on your Kindle in under a minute.

Don't have a Kindle? Get your Kindle here, or download a FREE Kindle Reading App.

Mastering Oracle SQL [Paperback]

Sanjay Mishra , Alan Beaulieu
3.8 out of 5 stars  See all reviews (4 customer reviews)
Price: £25.99 & FREE Delivery in the UK. Details
o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o
Only 9 left in stock (more on the way).
Dispatched from and sold by Amazon. Gift-wrap available.
Want it Sunday, 21 Dec.? Choose Express delivery at checkout. Details
‹  Return to Product Overview

Product Description

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.

Excerpt. © 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.

‹  Return to Product Overview