Oracle:SELECT

Main Page >> Oracle and SQL >> Workbook >> DML >> SELECT

Introduction to SELECT

You have already seen some simple SELECT statements that return all the rows back from the table. This is fine with a table with few records, but if you are working with large datasets, in most cases only a subset of the data would be required.

The rows returned can be restricted by use of the WHERE clause, e.g. WHERE DEPTNO = 30

The WHERE clause is one of the most important and complex clauses in the SELECT statement. It controls what rows are retrieved in the output, by applying a filter condition called a search condition.

Standard SQL search conditions can involve:

• Simple comparisons
• Compound conditions formed with AND, OR and NOT
• Special SQL predicates such as BETWEEN, IN, LIKE and NULL
• Comparison with the results of a subquery (a SELECT statement within a SELECT statement)
• Additional predicates designed for use with subqueries, such as ALL, ANY and EXISTS

This large range of possibilities makes the WHERE clause so complicated.

WHERE always works in the following manner:

• The search condition is applied to each prospective row in the output.
• If the condition is satisfied, then that row will be included in the output.
• If the condition is not satisfied, then that row is omitted from the output.

Comparison Operators

All search conditions are built from logical expressions, which are always true or false. The simplest form of a logical expression is known as a simple comparison, or relational predicate. Simple comparisons use comparison operators to compare two values.

Relational predicates appear in the WHERE clause and can include references to column names, literal constants, numeric or character expressions and non-aggregate functions.

The main rule to remember is that the two items being compared must of a compatible data type. You cannot mix numeric and character values in a comparison. Numeric with numeric, character with character and date with date are the only allowable forms.

Dates and character data values must also be included in single quotes (').

E.g., ename = SMITH is not allowed (SMITH would be treated as if it was a column name) but ename = 'SMITH' is allowed

The usual format of a simple comparison is:

Column-Name	Comparison-Operator	Value	Type
ename	<>	'SMITH'	character value
deptno	=	95	numeric value
hiredate	>	'11-JUL-1996'	date value

The following are valid comparison operators. NOT can be optionally used with the last four operators for negation:

	=	equal
	<>	not equal
	>	greater than
	>=	greater than, or equal to
	<	less than
	<=	less than, or equal to
[NOT]	BETWEEN .... AND ....	[not] between one value and another
[NOT]	IN ( list )	[not] in a list of values
[NOT]	LIKE	[not] like a value
	IS [NOT] NULL	value is [not] equal to null

Example Queries

Find all employees where their commission exceeds their salary:

SELECT  ENAME,  SAL,  COMM   FROM  EMP  
WHERE  COMM  >  SAL;

List information about all managers across all departments and clerks in department 10:

SELECT  * 
FROM EMP
WHERE JOB = 'MANAGER' OR 
(JOB = 'CLERK' AND DEPTNO = 10);

List names and departments of employees who are not clerks, analysts or salesmen:

SELECT ENAME, DEPTNO 
FROM EMP  
WHERE JOB NOT IN ('CLERK', 'ANALYST', 'SALESMAN');

List employees who have not earned any commission:

SELECT * FROM EMP WHERE COMM IS NULL;

List employees and jobs earning between £1,200 and £1,400 (no need to include the pound sign):

SELECT EMPNO, ENAME, JOB, SAL;
FROM EMP 
WHERE SAL BETWEEN 1200 AND 1400;

Note, the currency symbol, or the 1000 separator commas is not stored in the database.

List names, jobs and commission of employees who are not in department 10, 30 or 40:

SELECT ENAME, JOB, COMM 
FROM EMP 
WHERE DEPTNO NOT IN ( 10, 30, 40);

List information about employees in department 10 who are not managers or clerks:

SELECT * FROM EMP 
WHERE DEPTNO = 10 AND 
NOT (JOB = 'MANAGER' OR JOB = 'CLERK');

The LIKE operator

The LIKE operator is used for pattern matching.

This operator uses two symbols:

• % represents a string of characters
• _ represents a single character (underscore)

LIKE Examples

To list all employees whose name begins with S:

SELECT  *  FROM  EMP  
WHERE  ENAME  LIKE  'S%';

To list all employees whose name contains the characters LL:

SELECT * FROM EMP WHERE ENAME LIKE '%LL%';

List all the employees whose name ends in ER:

SELECT * FROM EMP WHERE ENAME LIKE '%ER';

List the employee number and name of employees who have A as the second letter of their name:

SELECT EMPNO, ENAME FROM EMP 
WHERE ENAME LIKE '_A%';

Preventing the Selection of Duplicate Rows

Some queries will produce results that include duplicate rows, for example, if you list rows without the primary key field(s).

For example, type in the following:

SELECT JOB FROM EMP;

A row will be returned for each record in the table. If you are only interested in seeing what jobs are stored in the table, rather than seeing how many employees there are for a particular job, use the DISTINCT clause.

To list all the distinct jobs in EMP:

SELECT DISTINCT JOB FROM EMP;

DISTINCT can be applied to more than one column:

SELECT DISTINCT JOB, COMM FROM EMP;

Note when using DISTINCT it must follow the SELECT clause and will apply to all the columns listed.

Controlling the order in which rows are displayed

You can not predict what order the results will appear from the database unless you take steps to sort it in some manner. This is why the results you have got so far may not match the examples given. They could be in the order they were inserted, or the database management system (DBMS) may have used some unknown indexes to sort the data.

The ORDER BY Clause

The ORDER BY clause lets you order rows:

• In ascending order
• In descending order
• Using multiple columns
• With null values

Note, an ORDER BY clause is always the last clause in a SELECT statement.

The format is:

ORDER BY <order_column> [ASC | DESC]

[, <order_column> [ASC | DESC] .....]

Order by sorts the query results based on the data in one, or more columns. Each order_column must correspond to a column in the query results and can be one of the following:

• A field in a FROM table that is a select item in the main SELECT clause (not in a subquery)
• A numeric expression indicating the location of the column in the result table (The leftmost column is number 1)

SELECT * FROM EMP 
ORDER BY JOB;

Specify DESC if you want the results in descending order:

SELECT * FROM EMP
ORDER BY JOB, SAL DESC;

ASCending is the default and can be omitted. Query results will appear unordered if the ORDER BY clause is missing. If you want all columns to be sorted in descending order, you do have to repeat the DESC for each field:

SELECT * FROM EMP
ORDER BY JOB DESC, SAL DESC;

Ordering with null values

When you order by columns that contain null (blank) values, the null entries can produce unpredicable results.

For numerical and character data, the nulls generally appear last (note, in previous versions they appeared first).

To list employees and jobs in department 30, in order of their commission:

SELECT ENAME, JOB, SAL, COMM FROM  EMP  
WHERE DEPTNO = 30 ORDER BY COMM;

Exercise 3.1

3.1.1 Find all salespeople in Department 30 whose salary is greater than or equal to £1,500:

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7499 ALLEN            1600         30
      7844 TURNER           1500         30

3.1.2 List information about managers and clerks in department 10:

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ----------
 7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
 7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

3.1.3 List the empno, name and jobs of employees whose names begin with M:

     EMPNO ENAME      JOB
---------- ---------- ---------
      7654 MARTIN     SALESMAN
      7934 MILLER     CLERK

3.1.4 List employees whose salaries are not between £1,200 and £1,400:

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7839 KING       PRESIDENT       5000
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7876 ADAMS      CLERK           1100
      7902 FORD       ANALYST         3000
      7369 SMITH      CLERK            800
      7698 BLAKE      MANAGER         2850
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500
      7900 JAMES      CLERK            950
      7782 CLARK      MANAGER         2450

11 rows selected.

3.1.5 List names and departments of employees who are clerks, analysts or salesmen:

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST           20
      7876 ADAMS      CLERK             20
      7902 FORD       ANALYST           20
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7654 MARTIN     SALESMAN          30
      7844 TURNER     SALESMAN          30
      7900 JAMES      CLERK             30
      7934 MILLER     CLERK             10

10 rows selected.

3.1.6 To list all the distinct Departments in EMP:

    DEPTNO
----------
        30
        20
        10

3.1.7 To sort employees by job and then in descending order by salary:

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7934 MILLER     CLERK           1300
      7876 ADAMS      CLERK           1100
      7900 JAMES      CLERK            950
      7369 SMITH      CLERK            800
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250

14 rows selected.

Next Step

Updating tables.