Natural Join
You can also use the ANSI SQL
syntax, which in many cases is easier to understand. The ANSI
natural join tells the database to join the two tables on the
common columns.
select
distinct
store_name,
book_key
from
sales natural join store
order by store_name,book_key;
While the natural join appears to be easy, I
personally do not like it because it is relying on the database
to determine which columns are the join columns. I prefer to
identify them in the query. It is easier to troubleshoot and
easier for someone else to look at your query and understand
what it is doing. The second ANSI join
format is very much like the original example except that all
the join information is located in the FROM clause leaving the
WHERE clause exclusively for filtering.
select
distinct
store_name,
book_key
from
sales join store on (store.store_key = sales.store_key)
order by store_name,book_key;
This format is cleaner to read and
understand, especially as the queries get more complicated.
The boss only wants those stores from South
Carolina, so we need to add the filter.
SQL>
select distinct
2 store_name,
3 book_key
4 from
sales join store on (store.store_key =
sales.store_key)
5 where
store_state = 'SC'
6 order by store_name,book_key;
STORE_NAME
BOOK_K
------------------------------------- ----------
eaton books
B102
eaton books
B104
eaton books
B106
eaton books
B107
eaton books
B108
eaton books
B109
eaton books
B113
eaton books
B116
hot wet and mushy books B102
hot wet and mushy books B103
hot wet and mushy books B104
hot wet and mushy books B105
hot wet and mushy books B106
hot wet and mushy books B107
hot wet and mushy books B108
hot wet and mushy books B109
hot wet and mushy books B110
hot wet and mushy books B111
18 rows selected.
Almost there! The boss also wants the book
titles, not the book_key.
For that, we will have to join to BOOK table into our query.
The BOOK table joins the SALES table on the
book_key.
SQL>
desc book
Name Null? Type
--------------------------- -------- -------------------------
BOOK_KEY VARCHAR2(6)
PUB_KEY VARCHAR2(4)
BOOK_TITLE VARCHAR2(80)
BOOK_TYPE VARCHAR2(30)
BOOK_RETAIL_PRICE VARCHAR2(30)
BOOK_ADVANCES VARCHAR2(30)
BOOK_ROYALTIES NUMBER(10)
BOOK_YTD_SALES NUMBER(10)
BOOK_COMMENTS VARCHAR2(200)
BOOK_DATE_PUBLISHED DATE
So, we are going to join the SALES, STORE
and BOOK tables together using the
store_key and the
book_key as shown in
Figure 3.2. The change to our query is minor, just add the join
to the FROM clause and change the
book_key to
book_title in the
SELECT and ORDER BY
clauses.
SQL>
select distinct
2 store_name,
3 book_title
4 from
5 sales join store on (store.store_key = sales.store_key),
6 join book on (sales.book_key = book.book_key)
7 where
store_state = 'SC'
8 order by store_name,book_title;
STORE_NAME
----------------------------------------
BOOK_TITLE
-----------------------------------------------------------------
eaton books
DOS for dummies
eaton
books
The zen of auto repair
eaton books
UNIX for experts
eaton
books
bears are people too
eaton
books
managing stress
eaton
books
non violins in the workplace
eaton
books
operations research theory
…
hot wet
and mushy books
zero loss finance
32 rows
selected.
Well, we got the information but we will
never get a raise giving the boss that. Since we now have the
query, let’s clean up the formatting, and since we know the boss
will be asking for the same report on another stats in the
future, we will add a SQL*Plus
variable to filter the state.
In this example, I used the SQL*Plus
ACCEPT command to prompt the user for a state and store the
results in a variable called statecode. The query then uses the
statecode variable in the WHERE clause. When I run the script
and enter SC for the state, I get the results below.
SQL> @store_state
Enter a State: SC
Store
Name Book Title
---------------------------- ----------------------------------
Eaton Books Bears Are People Too
Eaton Books Dos For Dummies
Eaton Books Managing Stress
Eaton Books Non Violins In The Workplace
Eaton Books Operations Research Theory
Eaton Books Oracle9i Sql Tuning
Eaton Books Pay No Taxes And Go To Jail
Eaton Books Piano Greats
Eaton Books Reduce Spending The Republican Way
Eaton Books The Fall Of Microsoft
Eaton Books The Willow Weeps No More
Eaton Books The Zen Of Auto Repair
Eaton Books Unix For Experts
Eaton Books Windows Success
Eaton Books Writers Market
Eaton Books Zero Loss Finance
Hot Wet And Mushy Books Bears Are People Too
Hot Wet And Mushy Books Dos For Dummies
Hot Wet And Mushy Books Managing Stress
Hot Wet And Mushy Books Non Violins In The Workplace
Hot Wet And Mushy Books Operations Research Theory
Hot Wet And Mushy Books Oracle9i Sql Tuning
Hot Wet And Mushy Books Pay No Taxes And Go To Jail
Hot Wet And Mushy Books Piano Greats
Hot Wet And Mushy Books Reduce Spending The Republican Way
Hot Wet And Mushy Books The Fall Of Microsoft
Hot Wet And Mushy Books The Willow Weeps No More
Hot Wet And Mushy Books The Zen Of Auto Repair
Hot Wet And Mushy Books Unix For Experts
Hot Wet And Mushy Books Windows Success
Hot Wet And Mushy Books Writers Market
Hot Wet And Mushy Books Zero Loss Finance
This report is acceptable, but remember, we are going for
the raise.
