You are here:   HomeStructured Query (SQL)SQL TutorialsSQL WHERE
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
SMITH 800 7369
ADAMS 1100 7876
JONES 2975 7566
SCOTT 3000 7788
FORD 3000 7902

 

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
JONES 2975 7566
SCOTT 3000 7788
FORD 3000 7902

 

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 SAL EMPNO
SMITH 800 7369
ADAMS 1100 7876
JAMES 950 7900
MILLER 1300 7934
ALLEN 1600 7499
WARD 1250 7521
MARTIN 1250 7654
TURNER 1500 7844
JONES 2975 7566
BLAKE 2850 7698
CLERK 2450 7782
SCOTT 3000 7788
FORD 3000 7902
KING 5000 7839

 

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
SMITH 800 7369
ADAMS 1100 7876
JONES 2975 7566
BLAKE 2850 7698
SCOTT 3000 7788
FORD 3000 7902
KING 5000 7839




Subscribe To Free Daily Newsletter!

Get Free News Updates Delivered Directly To Your Inbox
About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular Computer Notes 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.



What's New and Popular





Search Content







Advance Courses



Basic Courses



Advertise with Us