EnterpriseDB: Restricting Rows Oracle Tips by Burleson Consulting

Rarely will we want to see all of the records in a table.  In most instances, we will select on those rows that are most useful to us.  The ability to restrict rows is through the WHERE clause.

A where clause has many operators.  You may want to test for equality, i.e. ename = 'Lewis', you may want to test for inequality, i.e. ename != 'Lewis', or you may want to see all rows in a list, i.e. ename IN ('Lewis', 'Tom', 'Joe').   For numeric columns, you may want to test greater than (>), less than (<) or between (between x and y).  When you query dates, you may want to test for any of those.

The short story on the WHERE clause is that the evaluation of the expression (ename = 'Lewis') must evaluate to a Boolean.  That includes function calls or comparison operators.  When the expression evaluates to true, a record is retrieved.  Table 3.1 shows each of the available operators and provides a description and syntax.

OPERATOR	DESCRIPTION	SYNTAX
=	Equality	X = X, 1=1, '01-JAN-2005' = '01-JAN-2005'
!=	Inequality	X != Y, 1 != 2, '01-JAN-2005' != '02-JAN-2005'
>	Greater Than	'B' > 'A', 1 > 0, '02-JAN-2005' > '01-JAN-2005'
<	Less Than	'A' < 'B', 1 < 2, '01-JAN-2005' < '02-JAN-2005'
IN	In List	'a' IN ('a', 'b', 'c'), 1 IN (1,2,3), dates work also
IN <query>	In Query	'a' IN (SELECT 'a' FROM DUAL), numbers and dates can also be used
EXISTS	Match Exists	<query q> EXISTS (SELECT 1 FROM a WHERE a.y = q.y), any data type works
BETWEEN	Matches between two values	1 BETWEEN 0 AND 10, especially useful for dates, '01-JAN-2005' BETWEEN '01-JAN-2004' AND '01-JAN-2006'
LIKE	Wild card comparison.  % is a wildcard that can replace any number of characters and _ is a single character wildcard.	'A%' LIKE 'ABCD', Must be character data
NOT	Boolean Operator that negates other operators.  NOT must be used with another operator.	1 NOT IN (2,3,4), NOT EXISTS

Table 3.1: SQL Operators

Using these operators, we can query the emp table and return only those records with an ename of 'CLARK'.

SELECT ename
  FROM emp
  WHERE ename = 'CLARK';

ename
-------
 CLARK

(1 row)

Or we can query those rows that are not equal to 'Clark'.

SELECT ename
  FROM emp
  WHERE ename != 'CLARK'

ename
--------
 SMITH
 ALLEN
 WARD
 JONES
 MARTIN
 BLAKE
 SCOTT
 KING
 TURNER
 ADAMS
 JAMES
 FORD
 MILLER
 LEWIS

(14 rows)

We can query those rows that are in the list:  LEWIS, FORD, and ALLEN.

SELECT ename
  FROM emp
  WHERE ename IN ('LEWIS', 'FORD', 'ALLEN');

ename
-------
 ALLEN
 FORD

(2 rows)

Notice that LEWIS did not return a row even though we have a row with an ename of 'Lewis'.  To return the 'Lewis' row, we can upper case the column.

SELECT ename
  FROM emp
  WHERE UPPER(ename) IN ('LEWIS', 'FORD', 'ALLEN'); 

ename
-------
 ALLEN
 FORD
 LEWIS

(3 rows)

We can combine criteria to make a complex where clause.  We can select records where the job is 'SALESMAN', the empno is between 7200 and 7800 and the ename is in LEWIS, FORD, and ALLEN.

SELECT ename, job, empno
  FROM emp
  WHERE job = 'SALESMAN' AND
        empno BETWEEN 7200 AND 7800 AND
        UPPER(ename) IN ('LEWIS', 'FORD', 'ALLEN');

ename |   job    | empno
-------+----------+-------
 ALLEN | SALESMAN |  7499

(1 row)

Only ALLEN met the requested criteria.

Notice that I joined the different parts of the where clause with the keyword AND.  You can join the parts with OR or AND. If you join them with AND, all parts must be true. You can use parenthesis to combine AND and OR.

Now I will select records where (the job is 'SALESMAN' and the empno is between 7200 and 7800) or the ename is in LEWIS, FORD, ALLEN.

SELECT ename, job, empno
  FROM emp
  WHERE ( job = 'SALESMAN' AND
          empno BETWEEN 7200 AND 7800 )
        OR
          UPPER(ename) IN ('LEWIS', 'FORD', 'ALLEN');

ename  |   job    | empno
--------+----------+-------
 ALLEN  | SALESMAN |  7499
 WARD   | SALESMAN |  7521
 MARTIN | SALESMAN |  7654
 FORD   | ANALYST  |  7902
 Lewis  | CLERK    |  8000

(5 rows)

By expanding our criteria with OR, we managed to pick up some extra salesmen.  We also picked up Lewis and FORD who were not salesmen.

The final part of restricting data is the sub-query.  As I discussed above, a sub-query is a query called by an outer query.   Many times, a sub-query in a where clause will use the IN operator.

SELECT ename
  FROM emp
  WHERE deptno IN (
      SELECT deptno
        FROM dept
        WHERE dname = 'RESEARCH');

edb=# SELECT ename
edb-#   FROM emp
edb-#   WHERE deptno IN (
edb(#       SELECT deptno
edb(#         FROM dept
edb(#         WHERE dname = 'RESEARCH');

 ename
-------
 SMITH
 JONES
 SCOTT
 ADAMS
 FORD

(5 rows)

edb=#

Another way to accomplish this is with the EXISTS operator. 

SELECT ename
  FROM emp
  WHERE EXISTS (
      SELECT '1'
        FROM dept
        WHERE dname = 'RESEARCH'
          AND emp.deptno = dept.deptno); 

edb=# SELECT ename
edb-#   FROM emp
edb-#   WHERE EXISTS (
edb(#       SELECT '1'
edb(#         FROM dept
edb(#         WHERE dname = 'RESEARCH'
edb(#           AND emp.deptno = dept.deptno);

 ename
-------
 SMITH
 JONES
 SCOTT
 ADAMS
 FORD

(5 rows)

edb=#

In this example, the sub-query is called a correlated sub-query because the inner query (dept) is correlated to the outer query (emp) via the deptno comparison (emp.deptno = dept.deptno).

EnterpriseDB also has a neat feature to return only those rows you want.  You can use the LIMIT keyword to restrict the number of rows returned from a query.

By running, a simple query we can see how many rows our emp table has:

SELECT count(*) AS cnt FROM emp;

In my case, I get back 17 rows.  If I Only want to see the first 5, I can write my query using LIMIT:

SELECT ename
  FROM emp
  ORDER BY ename
  LIMIT 5;

edb=# SELECT ename
edb-#   FROM emp
edb-#   ORDER BY ename
edb-#   LIMIT 5;

 ename
-------
 ADAMS
 ALLEN
 BLAKE
 CLARK
 FORD

(5 rows)

edb=#

We can combine the LIMIT keyword with the OFFSET keyword to get ranges of data.  If we wanted to only retrieve 5 rows but we wanted the second 5.

SELECT ename
  FROM emp
  ORDER BY ename
  OFFSET 5
  LIMIT 5;

edb=# SELECT ename
edb-#   FROM emp
edb-#   ORDER BY ename
edb-#   OFFSET 5
edb-#   LIMIT 5;

 ename
--------
 JAMES
 JONES
 KING
 LEWIS
 MARTIN

(5 rows)

edb=#

In most other databases that I have used, getting ranges like this is a major pain and requires inner and outer selects or the use of analytic queries.  OFFSET and LIMIT can ease your coding burden in many situations.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.