From

The FROM clause specifies the source tables and views from which data is to be read. The specified tables and views must exist at the time the query is issued. The from_source parameter can be:

• One or more tables or views, specified using the following syntax:

  [schema.]table [[AS] corr_name]

  where table is the name of a table or view, or a synonym for a table or view.

• A join between two or more tables or views, specified using the following syntax:

  source join_type JOIN source ON search_condition

  or

  source join_type JOIN source USING (column {, column})

  or

  source CROSS JOIN source

  For details about specifying join sources, see ANSI/ISO Join Syntax.

• A derived table specified using the following syntax:

  (select_stmt) corr_name [(column_list)]

  where select_stmt is a select statement with no ORDER BY clause, corr_name is a mandatory correlation name, and column_list is an optional list of override names for the columns in the SELECT list of the select_list.

A maximum of 126 tables can be specified in a query, including the tables in the from list, tables in subselects, and tables and views resulting from the expansion of the definitions of any views included in the query.

Where

The WHERE clause specifies criteria that restrict the contents of the results table. Tests can be performed for simple relationships or, using subselects, for relationships between a column and a set of columns.

Simple Where Clauses

Using a simple WHERE clause, the contents of the results table can be restricted, as follows:

Comparisons:

select ename from employees
       where manager = 'Jones';
select ename from employees
       where salary > 50000;

Ranges:

select ordnum from orders
       where odate between date('jan-01-1993') and
       date('today');

Set membership:

select * from orders
       where partno in ('123-45', '678-90');

Pattern matching:

select * from employees
       where ename like 'A%';

Nulls:

select ename from employees
       where edept is null;

Combined restrictions using logical operators:

select ename from employees
       where edept is null and
       hiredate = date('today');

Note: Aggregate functions cannot appear anywhere in a where clause.