Oracle SQL Tips

Below I have documented some useful tips that I have found when working with SQL on Oracle.


The COUNT Function
When using the COUNT function to find the amount of rows that match match a particular criteria, many people use count(*) in this format:

select count(*) from f_customer where cus_balance > 0

The problem with this method is that it is slow. You can use a column from the table in the count function like this:

select count(cus_name) from f_customer where cus_balance > 0

The problem with this method is that if the column contains a NULL value then the row will not be counted. The foolproof way of using COUNT is as follow:

select count(ROWID) from f_customer where cus_balance > 0

This is the fastest and most reliable application of the COUNT function.


DUAL
Every select statement must have a table and a column. This is where DUAL comes in handy. DUAL is a ‘fake’ Oracle table and has many uses as a ‘scratchpad’ for retrieving answers from the database. Look at these examples:

select SYSDATE from DUAL - This returns todays data, from the Oracle server.

select 34267*2 from DUAL - This returns 68534


Column Alias
You can make your sql query results easier to read by making use of the ALIAS facility. This allows you to name the result column in a more meaningful way. For example:

select cus_code as CODE, cus_name as NAME, cus_balance as BALANCE from f_customer

This names the columns using the words shown in capitals rather than the column names. The ‘as’ can also be omitted if you like:

select cus_code CODE, cus_name NAME, cus_balance BALANCE from f_customer


ORDER BY
The ORDER BY facility is extremely useful and often used in the following way:

select cus_code CODE, cus_name NAME, cus_balance BALANCE from f_customer
order by cus_balance

You can however use the column position to sort the data the same way, ie: 1 for Column 1, 2 for Column 2 Etc.

select cus_code CODE, cus_name NAME, cus_balance BALANCE from f_customer
order by 3

You can even order by the ALIAS:

select cus_code CODE, cus_name NAME, cus_balance BALANCE from f_customer
order by BALANCE


Homer: Oh Lisa, there’s no record of a hurricane ever hitting Springfield.
Lisa: Yes, but the records only go back to 1978 when the hall of records was mysteriously blown away.