by Dinesh Thakur Category: SQL Tutorials

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.

 

ENAME

     SAL          EMPNO
SMITH8007369
ADAMS11007876
JONES29757566
SCOTT30007788
FORD30007902

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.

ENAME SAL EMPNO
JONES29757566
SCOTT30007788
FORD30007902

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;
ENAME SALEMPNO
SMITH8007369
ADAMS11007876
JAMES9507900
MILLER13007934
ALLEN16007499
WARD12507521
MARTIN12507654
TURNER15007844
JONES29757566
BLAKE28507698
CLERK24507782
SCOTT30007788
FORD30007902
KING50007839

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. 

ENAME SAL EMPNO
SMITH8007369
ADAMS11007876
JONES29757566
BLAKE28507698
SCOTT30007788
FORD30007902
KING50007839
Related SQL






About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.



Search Content