SQL Server: Reading notes from the book of "The Must-Know" (v)

5th Lesson Advanced Data Filtering

5.1 Combining WHERE clauses

The WHERE clause introduced in the 4th lesson is a single condition when filtering data.

5.1.1 and operator

Retrieve the names and prices of all products manufactured by Vendor DLL01 and priced at less than or equal to $4:

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

5.1.2 OR operator

Retrieve the names and prices of all products manufactured by any of the specified vendors:

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

5.1.3 Evaluation Order (())

You need to list all products that are priced at more than $10 and manufactured by DLL01 or BRS01:

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

　　

The results showed that 4 lines were priced at less than $10.

　　"Cause" SQL prioritizes and operators before processing OR operators

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

　　prompt uses parentheses in the WHERE clause:

Change the priority level;

Clear sequence of operations for enhanced readability.

5.2 In operator

In operator: Used to specify a range of conditions, each condition in the range can be matched.

Retrieve all products manufactured by supplier DLL01 and BRS01:

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

-- use in to complete and or the same operation SELECT Prod_name, Prod_price  from  Products WHERE = ' DLL01 ' OR = ' BRS01 ' ORDER  by Prod_name

reasons for using the in operator:
-in syntax is more intuitive when there are many legal options;
--when used in combination with other and and or, the Order of evaluation is easy to manage;
--in is usually executed faster than a group of OR;
--The greatest advantage--can include other SELECT statements, which can dynamically establish a WHERE clause.

5.3 Not operator

To negate any subsequent conditions.

Not never used alone

Not can be used before or after the column to be filtered

List products manufactured by all suppliers except DLL01:

SELECT Prod_name  from  Products WHERE  not = ' DLL01 ' ORDER  by Prod_name;

-- You can also use <> SELECT Prod_name  from  Products WHERE <> ' DLL01 ' ORDER  by Prod_name;

5.4 Summary

• WHERE clauses combined with and and OR
• Order of Evaluation
• In and not

Sequel:

SQL Server: Reading notes from the book of "The Must-know-all" (iv)

SQL Server: Reading notes from the book of "The Must-Know" (v)