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.
The list covered all the condition that you can give in the WHERE clause. The conditions include LIKE, Comparison, OR, AND, and IN etc.
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.
|S.No||Column 1||Operator||Column 2||Result|
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
- 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.
32 Complex SQL Queries
Useful for your interviews, practice and your project.
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.
Notes: In CASE statement, you cannot use the IF condition; Only WHEN condition you can use.
SELECT EMPNO, WORKDEPT, SALARY+COMM FROM EMPLOYEE WHERE (CASE WHEN SALARY=0 THEN 0 ELSE COMM/(SALARY+COMM) END) > 0.25;