SQL WHERE – Filtering Database Result Sets

sql whereThe SQL WHERE clause is one of the fundamentals of SQL and is taught early in any SQL training syllabus. The WHERE clause is a powerful and flexible tool that is used to refine and filter the results of not just a SELECT statement but also INSERT, UPDATE & DELETE. Learning the usage of the WHERE clause is the first step to writing efficient SQL queries.

Create effective, fast queries after taking a Udemy.com course

In a SELECT statement (the main SQL statement that retrieves data), there is a FROM clause, which tells SQL where to get data. The clause FROM is the first clause parsed by SQL when it processes the query. The FROM clause produces the result set for the SELECT statement. However, it may return more loosely qualified data than desired. If this is the case, then the SELECT statement needs to be more specific in what it is requesting. The mechanism to fine tune the result set produced by the preceding FROM clause is to apply a filter, and this is achieved using the WHERE clause.

The WHERE clause is simply a filter used to tighten the select criteria in order to return only the data that meets the strict requirements. An everyday example of applying a filter is when using a search engine. The less specific the search keywords the larger the result set. By making the search more specific and using filters, for example, on language or media type, the search narrows and returns a smaller more focused and relevant set of results. This is the same principle that is applied when a WHERE clause follows the FROM clause. Its purpose is to filter and refine the results returned.

In this simple scenario, the objective is to retrieve data for a specific vendor named Apple.

SELECT *

FROM Vendors

In the example above, the wildcard symbol * is used to designate all columns, so the SELECT is requesting all columns from the Vendors table.

vendor_name         vendor_id    Ranking   vendor_city

Samsung                    101                1            Seoul

HTC                          102              12             K.L

Apple                         103                2             L.A

Sony                         null               23            Tokyo

Nokia                        104               11            Stockholm

Acer                         105                33            Shanghai

That is a very inefficient select statement as it has returned everything in the table. Therefore, the select criteria needs to be more focused. This is why the WHERE clause is added — to filter the result set to contain only the rows with the vendor_name = ‘Apple’

SELECT *

FROM Vendors

WHERE vendor_name = ‘Apple’;

The WHERE clause is now applied, which sets a condition that will be used to refine and filter the result set. The SQL still parses and processes the FROM clause first and returns the full result set as before. However, SQL then processes the WHERE clause and applies the conditions to the result set. Each row is compared to the condition vendor_name = ‘Apple’ and tested if true. Only rows that return a true condition pass through the filter, and the rows that return false are filtered out. Therefore, the new refined SQL query contains only the row that matches the vendor_name “Apple”, which was the original objective.

vendor_name  vendor_id ranking  vendor_city

Apple                      103         2              L.A

In this example, SQL tested each row to determine the match. What if the objective was to return all vendors except ‘Apple’? Then, the test would need to be on the condition returning false. There are two approaches to take for this type of query using the WHERE clause:

  1. Use the not condition: WHERE NOT vendor_name = ‘Apple’
  2. Use the not equal to: WHERE vendor_name < > ‘Apple’

In the example above, a single condition was sufficient to focus the result set. However in practice the WHERE statement can be simple or complex depending on the requirements.

Using Where with AND

SELECT *
FROM Vendors
WHERE vendor_city = 'seoul'
AND vendor_id > 100;

In the example above, the result returned will be the vendors that match true for being in Seoul and having a vendor ID of greater than 100

vendor_name    vendor_id    Ranking   vendor_city

Samsung                 101               1             Seoul

 

Using WHERE with OR

SELECT vendor_id
FROM Vendors
WHERE vendor_name = 'Samsung'
OR vendor_name = 'Apple';

In this example, the criteria is for vendors that name the name Samsung or Apple

vendor_name   vendor_id    Ranking   vendor_city

Samsung                 101            1               Seoul

Apple                      103            2                L.A

 

WHERE combining AND & OR

SELECT *
FROM Vendors
WHERE (vendor_city = 'Seoul' AND vendor_name = 'Samsung')
OR (ranking <= 10);

Here the WHERE is combining two conditions the AND condition and the OR condition. The result will return vendors that match true to being located in Seoul and having the vendor name HTC or vendors who have a ranking less than 10.

vendor_name  vendor_id    Ranking   vendor_city

Samsung               101               1            Seoul

Apple                    103               2             L.A

Learn to master SQL with a course at Udemy.com

Using Where with Multiple Conditions

You can also use these simple conditions or comparisons in the WHERE clause when building a statement:

  • Relational-operator condition
  • IN or BETWEEN . . . AND
  • IS NULL or IS NOT NULL
  • LIKE or MATCHES

You also can use a SELECT statement within the WHERE clause; this is called a sub-query.

SELECT *
     FROM Vendors
     WHERE vendor_id IN (SELECT vendor_id
                  FROM Vendors
                  WHERE ranking > 10) ;

In the sub-query example above, a SELECT statement is run inside the WHERE clause and compared with the IN condition. All vendors’ columns (*) in the initial FROM result set are tested to check if their Vendor_id field are listed in the results from the sub-query, (the vendor_ids with a ranking greater than 10).

The following WHERE clause operators are valid in a sub-query:

  • IN or EXISTS
  • ALL, ANY, or SOME

 

The WHERE clause is a flexible and powerful clause for refining SELECT, UPDATE, INSERT, & DELETE statements for focus and efficiency.

Write fast, scalable SQL statements after taking a course at Udemy.com