 |
|
EnterpriseDB: In-Line View
Oracle Tips by Burleson Consulting
|
Instead of
just listing columns, you can also select functions and
expressions. I will describe all of the SQL functions below. In
this query I am calling a function named lower that will select all
of the enames in lower case text. In the same query, I am selecting
the empno column and am adding 10000 to each (this would be called
an expression).
SELECT
lower(ename) employee_name,
empno + 10000
FROM emp;
employee_name | ?column?
---------------+----------
smith | 17369
allen | 17499
ward | 17521
jones | 17566
martin | 17654
blake | 17698
clark | 17782
scott | 17788
king | 17839
turner | 17844
adams | 17876
james | 17900
ford | 17902
miller | 17934
lewis | 18000
(15 rows)
Notice that
the second column, where we added 10000 to the empno is listed as
?column?. That is because we did not use an alias. When you call a
function or use an expression in a select statement, you should
always give it an alias to avoid this issue.
Your FROM
may also be a dynamic table called an inline view. An in-line view
can be made up data such as:
SELECT foo.* FROM (
SELECT 'abc' col1 FROM DUAL ) AS foo;
edb=# SELECT foo.* FROM (
edb(# SELECT 'abc' col1 FROM DUAL ) AS foo;
col1
------
abc
(1 row)
edb=#
You may also
use an in-line view to select from a pre-selected and pre-sorted
data set:
SELECT foo.*
FROM (
SELECT ename
FROM emp
WHERE ename like 'A%'
ORDER BY ename DESC) AS foo;
edb=#
SELECT foo.* FROM (
edb(# SELECT ename
edb(# FROM emp
edb(# WHERE ename like 'A%'
edb(# ORDER BY ename DESC) AS foo;
ename
-------
ALLEN
ADAMS
(2 rows)
edb=#
A sub-query
may also be called directly from the SELECT clause of the
statement. In this case, the sub-query is called a scalar
sub-query. A scalar sub-query must always return exactly 1 column
and exactly 1 row.
SELECT
ename, (SELECT dname
FROM dept
WHERE emp.deptno = dept.deptno) AS dept_name
FROM emp;
edb=#
SELECT ename, (SELECT dname
edb(# FROM dept
edb(# WHERE emp.deptno = dept.deptno) AS dept_name
edb-# FROM emp;
ename | dept_name
--------+------------
SMITH | RESEARCH
ALLEN | SALES
WARD | SALES
JONES | RESEARCH
MARTIN | SALES
BLAKE | SALES
CLARK | ACCOUNTING
SCOTT | RESEARCH
KING | ACCOUNTING
TURNER | SALES
ADAMS | RESEARCH
JAMES | SALES
FORD | RESEARCH
MILLER | ACCOUNTING
Lewis |
|
|
(17 rows)
edb=#
In this
case, a JOIN (discussed below) would be a better solution than a
scalar sub-query but it does make the point.
When using
sub-queries, you should be aware of the impacts. We can call a
sequence's nextval in a query. What would we get if we called a
sequence's nextval in a scalar sub-query that returned multiple
rows?
SELECT
ename, (SELECT seq1.nextval
FROM DUAL) as seq_no
FROM emp;
edb=#
SELECT ename, (SELECT seq1.nextval
edb(# FROM DUAL) as seq_no
edb-# FROM emp;
ename | seq_no
--------+--------
SMITH | 25
ALLEN | 25
WARD | 25
JONES | 25
MARTIN | 25
BLAKE | 25
CLARK | 25
SCOTT | 25
KING
| 25
TURNER | 25
ADAMS | 25
JAMES | 25
FORD | 25
MILLER | 25
Lewis | 25
| 25
| 25
(17 rows)
edb=#
Was this the
result you expected? It seems counter intuitive to me. I wanted to
make the point that you should always test that what you think is
going to happen, really is going to happen.
What we have
learned so far is that the SELECT command begins with the SELECT
keyword. That keyword is followed by a column list (or expression
list). The column list is followed by the FROM keyword which lists
your table, tables or in-line views.
A sub-query
is a query referenced in an outer query. An in-line view is a
sub-query in the FROM clause. A scalar sub-query is a query that
returns exactly 1 column and exactly 1 row. A scalar sub-query can
be in the SELECT, FROM, WHERE, ORDER BY and GROUP BY clauses.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
