Methods of filtering data

The most basic command for filtering data is using SELECT statement’s WHERE clause to specify search conditions. Within a SELECT statement, data is filtered by specifying search criteria in the WHERE clause. The WHERE clause is specified right after the table name (the FROM clause) as follows:
select column_1, column_2 from table where column_1 > 5

Caution:
When using both ORDER BY and WHERE clauses, make sure that ORDER BY comes after the WHERE.

Another example:
select column_1, column_2 from table where column_1 != "seller01"

Caution:
!= and <> can usually be used interchangeably. However, not all DBMSs support both forms of the non-equality operator. You’d better checked documentation of DBMS for details.

Example continues, to check for a range of values:
select column_1, column_2 from table where column_2 between 5 and 10

Another important application of filtering is finding NULL value. The SELECT statement has a special WHERE clause that can be used to check for columns with NULL values—the IS NULL clause. The syntax looks like this:
select column_1 from table where column_2 is null

Now let’s see how to combine WHERE to create more sophisticated filtering:

1. AND:
   To filter by more than one column, we use the AND operator to add conditions to WHERE clause. The following code demonstrates this:
   select column_1, pcolumn_2, column_3 from table where column_1 = "seller3" and column_2 <= 10
   only rows matching all the specified conditions should be retrieved.

2. OR:
   To retrieve rows that match either condition. In most of DBMSs, if the first condition was met, the row would be retrieved regardless of the second condition. Check the following example:
   select column_1, pcolumn_2, column_3 from table where vcolumn_1 = "seller3" or column_3 = "certain item"

Order of evaluation

1. SQL processes AND operators before OR operators.
2. Parentheses have a higher order of evaluation than either AND or OR operators.

For example:
select vend_id, prod_name, prod_price from Products where vend_id = "DLL01" or vend_Id = "BRS01" and prod_price >= 10
The result is equal to:
select vend_id, prod_name, prod_price from Products where vend_id = "DLL01" or (vend_Id = "BRS01" and prod_price >= 10)

Tip:
Whenever you write WHERE clauses that use both AND and OR operators, use parentheses to explicitly group operators. Don’t ever rely on the default evaluation order, even if it is exactly what you want.

IN operator

select prod_name, prod_price from Products where vend_id in ("dll01","brs01") order by prod_name
The SELECT statement retrieves all products made by vendor DLL01 and vendor BRS01. The IN operator is followed by a comma-delimited list of valid values, and the entire list must be enclosed within parentheses. Above code has same effect as code beneath:
select prod_name, prod_price from Products where vend_id = "DLL01" or vend_id = "BRS01" order by prod_name

Tips: Advantage of using IN operator:

• When working with long lists of valid options, the IN operator syntax is far cleaner and easier to read.
• The order of evaluation is easier to manage when IN is used in conjunction with other AND and OR operators.
• IN operators almost always execute more quickly than lists of OR operators.
• The biggest advantage of IN is that the IN operator can contain another SELECT statement, enabling you to build highly dynamic WHERE clauses.

NOT operator

NOT is used to negate whatever condition comes next. For example:
select prod_name, vend_id from Products where not vend_id = "DLL01"
The NOT here negates the condition that follows it; so the DBMS matches vend_id to anything that is not DLL01.

Wildcards

Wildcards are special characters used to match parts of a value. Using wildcards can create search patterns that can be compared against data. To use wildcards in search clauses, the LIKE operator must be used. LIKE instructs the DBMS that the following search pattern is to be compared using a wildcard match rather than a straight equality match.

Tip:
Wildcard searching can only be used with strings, you can’t use wildcards to search fields of non-text datatypes.

1. The Percent Sign ( % )
   Within a search string, % means, match any number of occurrences of any character. For example, to find all products that started with the word Fish, you can issue the following SELECT statement:
   select prod_id, prod_name from Products where prod_name like "fish%"
   The % tells the DBMS to find out any characters after the word Fish, regardless of how many characters there are. Wildcards can be used anywhere within the search pattern, and multiple wildcards may be used as well:
   select prod_id, prod_name from Products where prod_name like "%bean bag%"
   The search pattern ‘%bean bag%’ means match any value that contains the text bean bag anywhere within it, regardless of any characters before or after that text.

Tips:

• It is important to note that, In addition to matching one or more characters, % also matches zero characters. % represents zero, one, or more characters at the specified location in the search pattern.
• % doesn’t match NULL

2. The Underscore ( _ )
   The underscore is used just like %, but instead of matching multiple characters the underscore matches just a single character. Following example uses Two underscore(_):
   select prod_name, prod_price from Products where prod_name like "__ inch teddy bear"

3. The Brackets ( [] )
   The brackets ([]) wildcard is used to specify a set of characters, any one of which must match a character in the specified position (the location of the wildcard). For example, to find all contacts whose names begin with the letter J or the letter M, we can do the following:
   select cust_contact from Customers where cust_contact rlike '[JM].*' order by cust_contact
rlike here means like in regular expression patterns.
   Adding ^ to negate: [^JM] .

Tips:

1. Wildcard searches typically take far longer to process than any other search types discussed previously.
2. Don’t overuse wildcards. If another search operator will do, use it instead.
3. When you do use wildcards, try to not use them at the beginning of the search pattern unless absolutely necessary. Search patterns that begin with wildcards are the slowest to process.
4. Pay careful attention to the placement of the wildcard symbols. If they are misplaced, you might not return the data you intended.

REF: SQL in 10 mins by Ben Forta