SQL WHERE Clause

The SQL WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. In a where clause simple conditions based on comparison operators can be combined using the logical connectives and, or, and not to form complex conditions. Conditions may also include pattern matching operations and even subqueries.

SQL> WHERE Syntax 
SELECT fields FROM table WHERE fields operator value 

To illustrate the usage of the SELECT where we are going to use the EMP Table:

List the ename, sal, empno whose deptno 20

Select ename, sal, empno From emp Where deptno = 20; 

The following table describes how “Where” selects a row.

The WHERE clause is an optional clause that can be used with the DELETE, SELECT, and UPDATE statements to specify a selection criteria. For all data types, the comparison operators =; != or <>; <; >;<=, => are allowed in the conditions of a where clause. SQL query used the “=” (Equal) operator in our WHERE Query:

deptno = 20

As you can see we have only selected the ename, sal, empno which entries have the value ‘20’ in the deptno field.

By using the AND logical operator we can further narrow the criteria to ‘sal’ who sal greater then 2500. The AND operator displays a record if both the first condition and the second condition is true.

Select ename,sal,empno From emp Where deptno = 20 AND sal >=2500; 

The following table describes how logical “AND” operator selects a row.

For example: If you want to find out the names of the employee whose mgr not 7365, the query would be like:

Select ename,sal,empno From emp Where not mgr = 7365; 

The following table describes how logical “NOT” operator selects a row.

If you want to find rows that do not satisfy a condition, you can use the NOT logical operator. NOT results in the reverse of a previous condition. That is, if a condition is satisfied, then the row is not returned.

By using the OR logical operator displays a record if either the first condition or the second condition is true.

Select ename,sal,empno From emp Where deptno = 20 OR sal >=2500;

The following table describes how logical “OR” operator selects a row.