Advanced data filtering using mysql

November 13, 2007

In this tutorial you will find how to combine WHERE clauses for creating strong searching conditions. You will find also how to use the operators NOT and IN.

Combining WHERE Clauses

These can be used in two ways: AND clauses, OR clauses.

Operators: Specialized reserved words used for clauses unification or modification.

AND Operator

If you want to filter for more columns, use AND operator to add conditions to WHERE clause. The next code represent this:

SELECT prod_id,prod_price,prod_name FROMProducts WHERE  sale_id =´DLL01´ AND prod_price  <= 4;

I used this instruction to find out the name and price for all the products sold by the DLL01 seller as long as the price is maxim 4$. This WHERE clause is made by two conditions, and the reserved word AND is used for the unifications of the two specified conditions.

Remember: AND is used for finding the lines which correspond all the specified conditions.

OR Operator

This operator is opposite the AND operator. Using this operator the result will contains lines which correspond one condition. The most database systems won’t evaluate the second condition as long as the first is true.

SELECT prod_name, prod_price FROM Products WHERE  sale_id = ´DLL01´ OR  sale_id  =´BRS01´;

This instruction will find the name and the price for all the products of one of the 2 sellers.

Remember: OR is used for finding lines which correspond one of the specified condition.

The evaluation order

WHERE clauses may contain as many AND, OR clauses. Combining this two type of operators will allow for a sophisticated and complexed filtering. There is a problem in using both AND, OR operators. For example we need the products list which cost minimum 10$ and are sold by the DLL01 and BRS01 sellers. The below instruction contains a combination of AND and OR clauses for building a WHERE clause.

SELECT prod_name, prod_price FROM Products WHERE sale_id=´DLL01´ OR  sale_id =´BRS01´AND prod_price  > =10;

The result will be:

Pen  3.49, Pencil  3.49, Notebook  4.49, Book  11.9.

If you look the result will see that the first results don’t have corresponding values so there were a problem. Why this happened? The answer is the evaluation order. SQL as the most operating languages works with AND operators before OR operators. The earlier WHERE clause is interpreted this way: “show me products which cost 10$ or more sold by the DLL01 seller and all the products sold by BRS01 no matter the price“. So the conclusion is that the operators where unified in a wrong way. To solve this problem use brackets. So look at the below instruction:

SELECT prod_name,  prod _price FROM  Products WHERE  (sale_id  = ´DLL01´ OR  sale_id  =  ´BRS01´)

We know that brackets has a highest priority, first will be analyzed the condition OR. So now we can interpret the SQL command: “show me all the products of the seller DLL01 or BRS01 which cost 10$ or more“.

Remember: When you have WHERE clauses which uses AND, OR operators use brackets to group in an explicitly way the operators.

IN Operator

We use this operator to specify a condition domain. IN necessities a list of valid values, which should be separated by commas and contents in brackets. Lets have an example in this way:

SELECT prod_name, prod_price FROM  Products WHERE sale_id  IN  (´DLL01´  ,´BRS01´) ORDER BY prod_name;

We can observe that IN can be substitute by the OR operator. SQL will give us the same result as in the earlier example:

SELECT prod_name, prod_price FROM Products WHERE sale_id  =´DLL01´ OR  vanz_id = ´BRSO1´ORDER  BY  prod_name;

So what are the IN advantages? When we have long lists of values using IN it is easiest to read. Almost always the IN operators are executed faster then OR lists. The big advantage is that IN may contain another SELECT instruction and this way you can build WHERE clause in a dynamic way.

NOT Operator

The NOT operator has a single function - deny any condition that comes after. The syntax for this operator is different. The next example show us how to use this operator. If you want to display the products of all the sellers less DLL01, you can write:

SELECT prod_name FROM Products

WHERE NOT sale_id  =´DLL01´ORDER  BY prod_name;

The operator in this case deny the condition that comes after it. The NOT operator can be substitute with the “<>” operator. For simple clauses as we have here is not necessary using NOT operator. It can be used in complex clauses.