WHERE clause in SQL is used for selective retrieval of rows from the table(s) i.e. output rows are fetched according to specific criteria (search condition). In SELECT statement, WHERE clause follows FROM clause and is used to specify the search condition. If the given search condition is satisfied then only it returns specific rows from the table(s). It acts as a filter so as to fetch only the required necessary rows.
Basic syntax for the WHERE clause –
Practice Section for WHERE clause : –
WHERE salary > 30000
Tips and Trics for WHERE clause :-
Tip 1. If you use LIKE in your WHERE clause, try to use one or more leading character in the clause, if at all possible.
Explanation :- If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an index to perform the query, speeding performance and reducing the load on SQL engine. But if the leading character in a LIKE clause is a wildcard, the Query Optimizer will not be able to use an index, and a table scan must be run, reducing performance and taking more time.
The more leading characters you can use in the LIKE clause, the more likely the Query Optimizer will find and use a suitable index.
What not to use:- LIKE ‘%ring’
What to use:- LIKE ‘php%’
Tip 2. When you have a choice of using the IN or the BETWEEN clauses use the BETWEEN clause, as it is much more efficient.
Explanation :– Query Optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause
SELECT emp_number, emp_name FROM phpring_employee WHERE emp_number in (1, 2, 3, 4, 5); is much less efficient than
SELECT emp_number, emp_name FROM phpring_employee WHERE emp_number BETWEEN 1 and 5;
Tip 3. Use of text functions in a WHERE clause hurts performance.
Explanation – Incase your database has case-sensitive data then you can go with the following to increase the efficiency and will run the query faster
What not to use :- SELECT column_name FROM table_name WHERE LOWER(column_name) = ‘name’;
What to use :- SELECT column_name FROM table_name WHERE column_name = ‘NAME’ or column_name = ‘name’;
WHERE clause can be implemented by number of ways depending upon the scenario. It is necessary to include a WHERE clause in your SELECT statement to narrow the number of rows returned. If you don’t use a WHERE clause, then SQL Server will perform a table scan of your table and return all rows.