Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

The SQL*Plus Break Command

 I can add some flash to this report by using the SQL*PlusBREAKcommand.  The BREAK command tells SQL*Plus to print a column once, then wait for the column to change.  Each time the column changes, SQL*Plus will print the new value once after skipping the defined number of lines.  Since this is another SQL*Plus command, it goes before the query in our script.  I am going to implement the BREAK command on the Store Name and skip two lines between stores.

Executing the script produces the report below.

SQL> @store_state
Enter a State:SC

Store Name                   Book Title

---------------------------- ----------------------------------Eaton Books                  Bears Are People Too                          DOS For Dummies
                             Managing Stress
                             Non Violins In The Workplace
                             Operations Research Theory
                             Oracle9i Sql Tuning
                             Pay No Taxes And Go To Jail
                             Piano Greats
                             Reduce Spending The Republican Way
                             The Fall Of Microsoft
                             The Willow Weeps No More
                             The Zen Of Auto Repair
                             Unix For Experts
                             Windows Success
                             Writers Market
                             Zero Loss Finance

Hot Wet And Mushy Books      Bears Are People Too
                             Dos For Dummies
                             Managing Stress
                             Non Violins In The Workplace
                             Operations Research Theory
                             Oracle9i Sql Tuning
                             Pay No Taxes And Go To Jail
                             Piano Greats
                             Reduce Spending The Republican Way
                             The Fall Of Microsoft
                             The Willow Weeps No More
                             The Zen Of Auto Repair
                             Unix For Experts
                             Windows Success
                             Writers Market
                             Zero Loss Finance

So, joins are how we get data from multiple tables.  I introduced a lot of items in the examples above, so let’s quickly review the joins.

The standard Oracle join format list the tables in the FROM clause and the join criteria in the WHERE clause. 

select distinct
  store_name,
  book_key
from
  sales, store
where
  store.store_key = sales.store_key
order by store_name,book_key; 

Using the ANSI SQL format, the tables and the join criteria are listed in the FROM clause.

select distinct
  store_name,
  book_key
from
  sales join store on (store.store_key = sales.store_key)
order by store_name,book_key;

The ON key word tells the database to limit the returned rows to those where the columns defined match, or don’t match in the case of an anti-join.  If the join columns have the same name, I can replace the ON clause with the USINGclause like below.

select distinct
  store_name,
  book_key
from
  sales join store using (store_key)
order by store_name,book_key;

Here, both tables have a column called store_key, and we are using them to join the tables.  We will discuss the USINGclause more when we talk about natural joins later in the chapter.

There are a number of different types of joins and each is used for a specific purpose.  Let’s look at the different types of table joins.

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.