Expressions Oracle Tips by Laurent Schneider

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

SQL expressions

Columns are not the only possible SQL expressions. Literal functions and pseudo columns are also valid SQL expressions.

1.      Column

A column from a table, view or subquery can be used as a SQL expression:

SELECT
   ENAME as "Employee name"
FROM
   EMP; 

The non-prefixed column ENAME is aliased as "Employee name":

SELECT
   EMP.ENAME
FROM
   EMP; 

The column ENAME is prefixed by the table name:

SELECT
   SCOTT.EMP.ENAME
FROM
   SCOTT.EMP; 

The column ENAME is prefixed by the table name and owner.

2.      Star

* is a joker which selects all columns from the table(s):

SELECT
   *
FROM
   EMP
JOIN
   DEPT
USING
   (DEPTNO); 

All columns are returned:

SELECT
   e.*
FROM
   EMP e
JOIN
   DEPT d
ON
   (e.DEPTNO=d.DEPTNO); 

All columns from EMP are returned.

Another valid expression is a date, number or character literal:

SELECT
   12,
   -34,
   .5
   -1.2e-10,
   100000000,
   1e200d,
   1e30f
FROM
   DUAL; 

Numeric literals can be selected with various notations.

Suffixes D and F indicate binary double and binary float (10g) . Binary doubles and binary floats offer better performance, allow numbers larger than 10¹²⁶ and smaller than 10^-131 and NaN (Not A Number) and Inf (Infinity).

SELECT
   POWER(2d,1000d) "2^1000",
   POWER(2d,10000d) "2^10000",
   POWER(-2d,10001d) "(-2)^10001",
   SQRT(-1d)
FROM
   DUAL; 

     2^1000    2^10000 (-2)^10001  SQRT(-1D)
---------- ---------- ---------- ----------
1.072E+301        Inf       -Inf        Nan

Binary doubles and binary floats offer additional literal values for NaN and Inf:

SELECT
   BINARY_DOUBLE_INFINITY,
   BINARY_DOUBLE_NAN,
   BINARY_FLOAT_INFINITY,
   BINARY_FLOAT_NAN
FROM
   DUAL; 

BINARY_DOUBLE_INF BINARY_DOUBLE_NAN BINARY_FLOAT_INF BINARY_FLOAT_NAN
----------------- ----------------- ---------------- -----------------
              Inf               Nan              Inf               Nan

Infinities and Not-a-Number values are returned.

There are additional tests for NaN and Inf:

SELECT
   1/0D,
   -1/0D,
   0/0D,
   1/1D,
   1/2D
FROM
   DUAL
WHERE
   1/0D IS INFINITE
   AND
   -1/0D IS INFINITE
   AND
   0/0D IS NAN
   AND
   1/1D IS NOT INFINITE
   AND
   1/2D IS NOT NAN;
 
     1/0D     -1/0D      0/0D      1/1D      1/2D
--------- --------- --------- --------- ---------
      Inf      -Inf       Nan  1.0E+000  5.0E-001

Checks are performed to test for NaN or Inf.

When BINARY_DOUBLE and BINARY_FLOAT are stored in an IEEE 754 conform binary format, NUMBER is stored in an Oracle proprietary format. Numbers offer a better precision than binary double and use space more efficiently in a varying length from 1 to 21 bytes:

SELECT
   VSIZE(1),
   VSIZE(1d)
FROM
   DUAL;
 

 VSIZE(1) VSIZE(1D)
--------- ---------
        2         8

SELECT
   1234567890d/9999999999d,
   1234567890/9999999999
FROM
   DUAL;
1234567890D/9999999999D                         1234567890/9999999999
----------------------- ---------------------------------------------
 .123456789012345680000  .1234567890123456789012345678901234567890000

Oracle offers various date formats:

SELECT
   DATE '2000-01-01',
   TIMESTAMP '2000-01-01 00:00:00.000',
   TIMESTAMP '2000-01-01 00:00:00.000 Europe/Zurich',
   TIME '00:00:00.000 Europe/Zurich',
   INTERVAL '1-01' YEAR(4) TO MONTH,
   INTERVAL '0.000000001' SECOND(6,9),
   INTERVAL '-1 12' DAY(6) TO HOUR
FROM
   DUAL 

DATE is an Oracle proprietary format. The granularity is one second and the range is from 01-Jan-4712 B.C. to 31-Dec-9999 A.D. at 23h59m59s.

The unit when adding a numeric literal is one day:

SELECT
   DATE '2007-12-31' + 60
FROM
   DUAL; 

DATE'2007
---------
29-FEB-08

The sixtieth day in 2015 is a leap year day.

Timestamp offers a finer granularity up to the nanosecond. Timestamp optionally contains a time zone.

SELECT
   TIMESTAMP '2009-01-01 00:00:00.000000001 Europe/Zurich'
      AT TIME ZONE 'US/Central'
FROM
   DUAL;

TIMESTAMP'2009-01-0100:00:00.000000001EUROPE/ZURICH'ATTIMEZONE'US/CENTRAL'
---------------------------------------------------------------------------
2008-12-31 17:00:00.000000001 US/CENTRAL

The syntax above converts a timestamp to a different time zone. One type of interval is the day-to-month interval. The granularity is one month and the range is from minus one billion year to plus one billion year, exclusively: 

SELECT
   INTERVAL '1' YEAR + 2 * INTERVAL '6' MONTH
FROM
   DUAL; 

INTERVAL'1'YEAR+2*INTERVAL'6'MONTH
----------------------------------
+000000002-00

Addition and multiplication of intervals is possible, but aggregate functions like SUM and AVG cannot be used with this datatype. The second type of interval is the day-to-second interval. The granularity is a nanosecond and the range is from minus one billion day (-10⁹ days) to plus one billion day (+10⁹ days), exclusively:

SELECT
   TIMESTAMP '2008-03-30 01:59:59 Europe/Zurich' + INTERVAL '1' SECOND
FROM
   DUAL; 

TIMESTAMP'2008-03-3001:59:59EUROPE/ZURICH'+INTERVAL'1'SECOND
------------------------------------------------------------
2008-03-30 03:00:00 EUROPE/ZURICH

Interval can be added to timestamp. In the example above,  the daylight saving time capabilities of timestamp is shown.

Another basic datatype of Oracle is the character datatype, of which there are two: CHAR and VARCHAR2. Oracle recommends using VARCHAR2. A literal is always CHAR. Both VARCHAR2 and CHAR could be stored in the default character set or in Unicode as National Char (NCHAR) or Varchar2 (NVARCHAR2). VARCHAR2 saves disk space and, therefore, usually enhance performance as less blocks must be read.

SELECT
   'Hello',
   n'World',
   q'[it's Friday]',
   nq'{'!'}',
   '''text in quotes'''
FROM
   DUAL;
'HELL N'WOR Q'[IT'SFRID NQ' '''TEXTINQUOTES'
----- ----- ----------- --- ----------------
Hello World it's Friday '!' 'text in quotes'

A string literal is always in single quotes. A quote within the string is doubled, like in '5 O''Clock'.

Oracle 10g introduced the prefix q. When prefixed by q, the string uses special characters to delimit the string. N prefixes a national character set string (Unicode).