4. Advanced Data Filtering---SQL

One, and operator

To filter by more than one column, you can use the a nd operator to attach conditions to the WHERE clause.

SELECT prod_id, Prod_price, Prod_name  from  Products WHERE = ' DLL01 '  and <= 4;

Analysis
This SQL statement retrieves the names and prices of all products manufactured by Vendor DLL01 and priced less than or equal to $4 . The WHERE clause in this SELECT statement contains two conditions,
Join together with the a nd keyword. A nd instructs the DBMS to return only the rows that satisfy all the given criteria. If a product is manufactured by a vendor DLL01, but the price is higher than $4, it does not retrieve
It. Similarly, if the product price is less than $4, but is not manufactured by the designated supplier and is not retrieved

* Multiple filters can be added to use the a nd keyword between each condition.

Second, or operator

The OR operator is the exact opposite of the A nd operator, which instructs the DBMS to retrieve rows that match either condition. In fact, many DBMS are satisfied with the first condition of the OR WHERE clause
, the second condition is no longer evaluated (when the first condition is satisfied, the corresponding row is retrieved, regardless of whether the second condition is satisfied)

SELECT Prod_name, Prod_price  from  Products WHERE = ' DLL01 ' OR = ' BRS01 ';

Analysis
This SQL statement retrieves the product name and price for all products manufactured by any one of the specified vendors. The OR operator tells the DBMS to match either condition instead of matching two conditions at a time. If this
With the A nd operator, no data is returned (because a WHERE clause with no matching rows is created).

OR
The keyword used in the WHERE clause to represent the retrieval of rows that match any given condition.

Iii. Order of Evaluation

 select   Prod_name, Prod_price  from   products  where  vend_id Span style= "color: #808080;" >=   " dll01    and  prod_price >=  Span style= "color: #800000; Font-weight:bold; " >10 ; 

  output prod_name prod_price  -- --------------------------- Fish bean bag toy 3.4900  bird bean bag toy   3.4900  rabbit bean bag toy  3.4900  11.9900  raggedy Ann  4.9900  

Analysis
Please look at the results above. There are 4 rows in the returned row with a price less than $10, and it is clear that the rows returned are not filtered as expected. Why is that? The reason is the order in which the values are evaluated.
SQL (like most languages) takes precedence over the a ND operator before processing the OR operator. When SQL sees the WHERE clause above, it is understood to be: Price manufactured by Vendor BRS01
For all products up to US $10 and all products manufactured by supplier DLL01 regardless of their price. In other words, because a ND has higher precedence in the evaluation process, the operator
were wrongly combined.

The workaround for this problem is to use parentheses to group the operators in an explicit way. Take a look at the following SELECT statement and output:

 select   Prod_name, Prod_price  from   products  where  (vend_id Span style= "color: #808080;" >=   " dll01    and  prod_price >=  Span style= "color: #800000; Font-weight:bold; " >10 ; 

Output Prod_name Prod_price -- ----------------- ----------  - 11.9900

Analysis
The only difference between this SELECT statement and the previous one is that the first two conditions are enclosed in parentheses. Because parentheses have a higher order of evaluation than a nd or OR operator, the DBMS
First filter the OR condition within parentheses. At this point, the SQL statement became the choice of all products manufactured by supplier DLL01 or BRS01 and priced at $10 and above, which is exactly what we want
The result.

Tip: Use parentheses in the WHERE clause
Any time you use a WHERE clause with a nd and OR operator, you should use parentheses to explicitly group the operators. Do not rely too much on the default evaluation order, even if it does
Look at that. There is nothing bad about using parentheses, it can eliminate ambiguity.

Four, in operator

The in operator is used to specify a range of conditions, and each condition in the range can be matched.

In takes a set of valid values separated by commas, enclosed in parentheses

SELECT Prod_name, Prod_price  from  Products WHERE inch ' DLL01 ' ' BRS01 ' )ORDER by Prod_name;

Output Prod_name Prod_price------------------- ---------- AInch Teddy Bear8.9900 -Inch Teddy Bear11.99008Inch Teddy Bear5.9900Bird Bean bag Toy3.4900Fish Bean bag Toy3.4900Rabbit Bean bag Toy3.4900raggedy Ann4.9900

Analysis
This SELECT statement retrieves all products manufactured by the vendor DLL01 and BRS01. The in operator is followed by a comma-delimited list of valid values that must be enclosed in parentheses.

You may guess that the in operator completes the same function as or, congratulations!

Why do I use the in operator? The advantages are:
* When there are many legal options, the in operator syntax is clearer and more intuitive.
* In combination with other and and or operators, the order of evaluation is easier to manage.
The *in operator is generally faster than a set of or operators (in the case of a few legitimate options above, you do not see performance differences).
The biggest advantage of *in is that it can contain other SELECT statements, which can create a WHERE clause more dynamically.
Inch
The keyword in the WHERE clause that specifies the manifest to match the value, and the function is comparable to or.

Five, not operator

The not operator in the WHERE clause has only one function, which is to negate any subsequent conditions. Because not is never used alone (it is always used with other operators),

So its syntax differs from other operators. The NOT keyword can be used before the column to filter, not just after it.

not
The keyword used in the WHERE clause to negate the subsequent condition.

Analysis
Why use not? For this simple WHERE clause here, the use of not does not really have any advantage. But in more complex clauses, not is very useful. For example, in
When used in conjunction with the in operator, not it is very simple to find rows that do not match the list of conditions.
Description: Not in the MARIADB
MARIADB supports the use of not negation in, between, and exists clauses. Most DBMS allows the use of not to negate any condition.

4. Advanced Data Filtering---SQL