DISTINCT and WHERE clause in SQL

Read this Oracle Tutorial Point article to know how to use DISTINCT and WHERE clause and what are all conditions to follow while using these clauses in SQL in Oracle Database.

DISTINCT CLAUSE:
Until it is instructed SQL * Plus displays the results of a query while not eliminating duplicate rows. To eliminate the duplicate rows in the result the DISTINCT keyword is used. Multiple columns are declared next to the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns and describe a distinct combination of the columns.

Example:

SQL> SELECT DISTINCT DNO FROM EMP;

SQL> SELECT DISTINCT JOB, DNO FROM EMP;

WHERE CLAUSE:
The number of rows returned by a query can be limited/filter by using the WHERE clause. A WHERE clause contains a restriction that must be met and should directly follow the FROM clause.

Syntax:

SELECT [DISTINCT] {*|Column1 [alias],Column2 [alias],....} FROM TABLE 

       [WHERE condition(s)];

The WHERE clause can compares,

• Value in columns
• Arithmetic Expressions
• Literal Values
• Functions

The components of WHERE clauses are,

• Column Name
• Comparison Operator
• Column Name, Constants or list of values.

The character string and date should be enclosed in single quotes marks. Character value is case sensitive and date value is format sensitive {DD-MMM-YY}. The comparison operator is used in conditions that compare one expression to a different expression. The different comparison operators are (=, <>, != ,^=,, <, <=,>, >=). The format of the WHERE clause is WHERE expr operator value.

Examples:

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='MANAGER';

SQL> SELECT ENAME,HIREDATE,DNO,SAL  FROM EMP WHERE DNO=10;

SQL> SELECT ENO,ENAME,SAL  FROM EMP WHERE SAL>=5000;

SQL> SELECT ENAME || 'JOINED On' || HIREDATE 'EMPLOYEES JOINING DATES' FROM EMP 

WHERE HIREDATE='01-OCT-90';

SQL> SELECT ENAME || 'Works in Department' || DNO 'EMPLOYEES  AND DEPARTMENTS' FROM EMP 

WHERE DNO<>20;

SQL> SELECT ENAME,SAL,DNO,JOB FROM EMP WHERE JOB <> 'CLERK';