SQL Query Examples on Multiple WHERE Conditions

Multiple conditions, how to give in the SQL WHERE Clause, I have covered in this post. Those are IN, LT, GT, =, AND, OR, and CASE. It takes more CPU time, If the WHERE condition is not proper, to fetch rows – since more rows.

• IN – List
• GT – Greater than
• LT – Less than

1. SQL WHERE Clause ‘Equal’ or ‘LIKE’ 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. Result of NULL Value Comparision.

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.

• You May Also Like: Oracle Database 12c SQL Certified Associate 1Z0-071

3. Multiple SQL Where Clause Conditions – Like >, >=, <, <=, AND and OR

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

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 in 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