Introduction to SQL: 5.Advanced Data Filtering

3 min read SQL

This post is heavily based on Sams Teach Yourself SQL in 10 Minutes (Fourth Edition). Please consider to buy the book if you find this post useful.

Combining WHERE Clauses

We can specify multiple WHERE clauses. These clauses may be used in two ways: as AND clauses or as OR clauses.


Using the AND Operator

This is used to filter by more than one column.

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

AND instructs the database management system software to return only rows that meet all the conditions specified. If a product is made by vendor DLL01, but it costs more than $4, it is not retrieved. Additional filter conditions could be used as well, each separated by an AND keyword.


Using the OR Operator

The OR operator instructs the database management system software to retrieve rows that match either condition. It is the opposite to AND. For example:

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

The above SQL statement retrieves the product name and price for any products made by either of the two specified vendors.


Understanding Order of Evaluation

Let’s list of all products costing $10 or more made by vendors DLL01 and BRS01.

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
AND prod_price >= 10;

The output is shown below:

One can observe that four of the rows returned have prices less than $10. Why is that so?
This is caused by the orede of evaluation. SQL (like most languages) processes AND operators before OR operators. Thus, it was read as any products costing $10 or more made by vendor BRS01, and any products made by vendor DLL01 regardless of price.
The solution is to add in parentheses to explicitly group related operators because parentheses have a higher order of evaluation.

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price >= 10;


Using the IN Operator

IN is a keyword used in a WHERE clause to specify a list of values to be matched using an OR comparison. However, it has several advantages:

  • Cleaner and easier to read syntax.
  • The order of evaluation is easier to manage when IN is used in conjunction with other AND and OR operators.
  • Execute faster.
  • The IN operator can contain another SELECT statement (known as subqueries).
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;


Using the NOT Operator

NOT is a keyword used in a WHERE clause to negate a condition. It can be used before the column to filter on, not just after it.

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

The same thing can be accomplished by using < >.

SELECT prod_name
FROM Products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;

NOT is useful in more complex clauses. For example, using NOT in conjunction with an IN operator makes it simple to find all rows that do not match a list of criteria.

SQL TYSQL

Related

Next
Previous