5. Basic-Data filtering (Where)

combine WHERE clause

operator: a keyword used to join or change a clause in a WHERE clause. Also known as logical operators. ADN operator

Input: SELECT Prod_id,prod_price,prod_name
From Products

WHERE vend_id = 1003 and Prod_price <= 10;

Analysis: The WHERE clause contains two conditions and is joined with the and keyword. In fact, you can add multiple filter conditions, each add one will use a ADN.

and : used in the WHERE clause, which requires that all given conditions be met. or operator

Input: SELECT Prod_id,prod_price,prod_name
From Products

WHERE vend_id = 1003 OR prod_price <= 10;

Analysis: only one condition can be satisfied. Order of calculation

A where can contain any number of and and or operators, and allows both to be combined for complex and advanced filtering.

Input: SELECT Prod_id,prod_price,prod_name
From Products

WHERE vend_id = 1002 OR vend_id = 1003 and Prod_price >= 10;

Analysis: The AND operator is treated preferentially before the OR operator is processed. In fact, vend_id = 1002 OR (vend_id = 1003 and Prod_price >= 10);

Parentheses have higher precedence than and and OR. You should use parentheses as much as possible and do not expect the default calculation order. In operator

The in operator is used to specify a range of conditions in which each condition can be matched. In the following is a comma-delimited list of valid values, all enclosed in parentheses.

Input: SELECT Prod_id,prod_price,prod_name
From Products

WHERE vend_id in (1002.1003)
ORDER by Prod_name;

Analysis: The use of in is similar to or somewhat. But in a more advantageous position.

1. In more intuitive, when the election is more obvious;

2. Better management of the order of options;

3.IN executes faster than or;

The biggest advantage of 4.IN is that you can include other SELECT statements that allow you to create a WHERE clause more dynamically. Not operator

Input: SELECT Prod_id,prod_price,prod_name
From Products

WHERE vend_id not in (1002.1003)

ORDER by Prod_name;

Analysis: Not support for use with In,between and exists.