Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 38 ยท Getting Started

First Queries: SELECT and DUAL

Write your first Oracle queries: SELECT lists, column aliases, concatenation with ||, DISTINCT, arithmetic, and the one-row DUAL table used for expressions.

A query starts with SELECT. You list what you want, say which table it comes from, and Oracle returns rows. Because every Oracle SQL statement shares this skeleton, this lesson builds the habits that make later queries easy: clear column lists, aliases, expressions and DISTINCT.

It also introduces DUAL, a one-row table that Oracle created so that SELECT always has something to select from. In 23ai you can leave it out, but you will meet it in nearly every older script.

The SELECT statement

The basic form is SELECT columns FROM table. This runs unchanged in Oracle:

SQL Selecting columns ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SELECT * returns every column. It is fine for a quick look, but production code should list the columns it needs. A new column added later would otherwise silently change what the query returns.

Keywords and unquoted names are not case-sensitive in Oracle: select, SELECT and Select are the same. Oracle stores unquoted object names in upper case, so employees and EMPLOYEES refer to the same table. If a name was created with double quotes ("Employees"), you must always write it with quotes and the exact case. Avoid that style.

Column aliases

An alias gives a result column a new name. Use AS or just a space:

SQL Aliases and arithmetic ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

In Oracle, end the query with FETCH FIRST 5 ROWS ONLY instead of LIMIT 5; we cover that in filtering and sorting. The editor uses SQLite, so LIMIT is the stand-in here.

A few alias rules in Oracle:

  • An unquoted alias is shown in upper case (MONTHLY).
  • A double-quoted alias keeps its case and may contain spaces: "With 5% raise".
  • You cannot use a SELECT alias in the same query's WHERE clause (Oracle evaluates WHERE first). 23ai lets you use aliases in GROUP BY and HAVING.

Concatenation with ||

The Oracle concatenation operator is ||, the same as the SQL standard. NULL is treated as an empty string in a concatenation:

SQL Building text with || ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Oracle also has the CONCAT(a, b) function, but it accepts only two arguments, so || is easier to chain. Quotes inside a string are doubled ('It''s'), or you can use the alternative quoting operator: q'[It's easy]'.

DISTINCT

DISTINCT removes duplicate rows from the result. It applies to the whole row, not to one column:

SQL Removing duplicates ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Oracle also accepts UNIQUE as a synonym for DISTINCT, but DISTINCT is the standard spelling.

The DUAL table

DUAL is a table owned by SYS with a single column DUMMY and a single row (the value X). Oracle needed every SELECT to have a FROM clause, so when you only want to evaluate an expression, you select from DUAL:

SQL
SELECT 2 + 3 AS total FROM dual;
SELECT SYSDATE FROM dual;
SELECT USER FROM dual;
SELECT UPPER('oracle') FROM dual;

The first query returns one row with the value 5. SYSDATE returns the current date and time of the database server. USER returns the name of the connected user.

Starting with Oracle Database 23ai, FROM is optional:

SQL
SELECT 2 + 3 AS total;

Scripts that must also run on 19c should keep FROM dual. Because the editor below has no DUAL table, the stand-in drops the FROM clause, which SQLite allows:

SQL Stand-in for SELECT ... FROM dual ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Pseudocolumns

A pseudocolumn behaves like a column but is not stored in the table. The ones you will see most:

Pseudocolumn Meaning
ROWNUM Number assigned to each row as it is returned (1, 2, 3, ...)
ROWID The physical address of a row
SYSDATE Current date and time of the server (this is a function, not a column, but is used the same way)
LEVEL Depth in a hierarchical query (CONNECT BY)
SQL
SELECT ROWNUM, employee_id, last_name
FROM employees
WHERE ROWNUM <= 3;

This returns the first three rows Oracle happens to produce. There is no guaranteed order without ORDER BY, a rule you should remember for every database.

NULL in expressions

NULL means "unknown". Any arithmetic with NULL gives NULL, and comparing with = never matches it. Use IS NULL and IS NOT NULL:

SQL NULL handling ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the Oracle table DUAL for?

    DUAL has one row and one column. Selecting from it lets you run expressions such as SELECT SYSDATE FROM dual.
  2. 2. Which is the Oracle string concatenation operator?

    || is the concatenation operator. CONCAT exists but takes only two arguments.
  3. 3. A table holds the name Ada in first_name and NULL in nickname. What does first_name || nickname return?

    In concatenation Oracle treats NULL as an empty string, so the result is Ada.
  4. 4. Why is SELECT * discouraged in production code?

    Listing columns makes queries stable and clear. SELECT * is fine for exploration.

Summary

  • SELECT columns FROM table is the core of Oracle SQL; list the columns you need.
  • Aliases rename result columns; quote them to keep case or spaces.
  • || concatenates strings, and NULL counts as empty text in a concatenation.
  • DISTINCT removes duplicate rows; DUAL lets you evaluate expressions (optional from 23ai).
  • Pseudocolumns such as ROWNUM and ROWID are available in queries but are not stored columns.

Next: Filtering and Sorting in Oracle