Oracle SQL - Selecting an Expression with Literals

  1. HOME
  2. Oracle SQL
  3. Select
  4. Column Expressions

Introduction

Consider the following code: 

select 3 + 4 from departments;

The query result might look strange at first.

The outcome of the expression 3+4 is calculated for each row of the DEPARTMENTS table.

This is done four times, because there are four departments and we did not specify a WHERE clause.

Because the expression 3+4 does not contain any variables, the result (7) is obviously the same for every department row.

Demo

SQL>
SQL>-- w w  w  .  ja v a2 s. c  o m
SQL> drop table departments;

Table dropped.

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL> select 3 + 4 from departments;

      3+4
---------
 00007.00
 00007.00
 00007.00
 00007.00

SQL>

Related Topic