SQL Query Examples on Multiple WHERE Conditions

In this post, I am sharing with you ideas on how to give multiple conditions in SQL WHERE Clause.

I want to tell the power of the WHERE clause. And you can give multiple conditions like IN, less than, greater than, equal to, AND, OR, and CASE conditions. If the condition is not proper, the query will fetch a lot of rows. As to it takes more CPU time.


I am sharing with you pretty good SQL queries examples on multiple conditions how you can give in SQL WHERE clause.

SQL Where Clause
In SQL query the WHERE clause basically helps you to filter the records based on certain criteria. In this post explained about all the possible conditions that you can give.

#1. SQL WHERE Clause ‘Equal’ Condition

SELECT MY_NAME
FROM EMPLOYEE
WHERE MY_NAME LIKE %SRI%
or
SELECT *
FROM EMPLOYEE
WHERE MY_NAME = 'SRINIMF'

In the above example, it filters out the names only contain “SRI”.

SELECT *
FROM EMPLOYEE
WHERE MY_BONUS = '959.00'

After executing this query you will get all the details whose bonus equal to “959.00”.



#2. Bonus Topic on SQL NULL value Comparison

S.NoColumn 1OperatorColumn 2Result
1NULL+NULLNULL
2NULLNULLNULL
3NULL/NULLNULL
4NULL*NULLNULL
5NULL>NULLUNKNOWN/FALSE
6NULL>=NULLUNKNOWN/FALSE
7NULL<NULLUNKNOWN/FALSE
8NULL<=NULLUNKNOWN/FALSE
9NULL=NULLUNKNOWN/FALSE
10NULL<>NULLUNKNOWN/FALSE
11NULL>NULLUNKNOWN/FALSE
12NULL<NULLUNKNOWN/FALSE
13NULL>=NULLUNKNOWN/FALSE
14NULL<=NULLUNKNOWN/FALSE
15NULL+NULLUNKNOWN/FALSE
16NULL<>NULLUNKNOWN/FALSE

How to Filter the Rows for an Employee whose Bonus not Entered

SELECT *
FROM EMPLOYEE
WHERE MY_BONUS IS NULL

The result of above query will be who does not get any bonus.

Best Seller: Oracle Database 12c SQL Certified Associate 1Z0-071

#3. Multiple conditions like >, >=, <, <=, AND and OR in SQL WHERE clause

How the order of precedence during the execution of SQL refer to the below chart

1). ()

2). AND

3). NOT

4). OR

Let us see now an SQL query with all the details

SELECT *
FROM EMPLOYEE
WHERE (MY_SALARY='90000' or MY_BONUS IS NULL)
AND MY_NAME LIKE %SRI%

When you run the above query it first evaluates ‘()’, then AND, then OR.

#4. How to give ‘IN’ list condition SQL WHERE clause

SELECT *
FROM EMPLOYEE
WHERE MY_NAME IN ('SRINIMF', 'MOHNA', 'TORAD')

It returns all the matching rows. Using IN list is time saving idea and you can reduce size of your query.

#5. How to give ‘CASE’ condition in SQL WHERE Clause

SELECT EMPNO, WORKDEPT, SALARY+COMM 
FROM EMPLOYEE
WHERE (CASE WHEN SALARY=0 THEN 0
            ELSE COMM/(SALARY+COMM)
            END) > 0.25;

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.