WHERE clause in SQL Query

2
1751

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 –

SELECT column_list

FROM table_name

WHERE [search_condition]

Where clause

Practice Section for WHERE clause : –

SELECT emp_id,emp_name,salary

FROM phpring_employee

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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.9/10 (26 votes cast)
VN:F [1.9.22_1171]
Rating: +25 (from 25 votes)
WHERE clause in SQL Query, 9.9 out of 10 based on 26 ratings
  • Vivek

    Hi,
    Very nice way of explaining where clause using cartoon images
    Thanks

  • prince

    Tips and Tricks section is very helpful !!!