SQL Essentials: IN, BETWEEN, LIKE, NULL, and Aggregates

IN and BETWEEN

• IN: list of values to compare against; numbers are unquoted, text requires single quotes.
• Example concept: WHERE quantityonhand IN (100, 150, 140, 150) (numbers shown without quotes).
• BETWEEN: range of values; inclusive boundaries.
• Example concept: WHERE quantityonhand BETWEEN 100 AND 200
• BETWEEN is equivalent to: WHERE quantityonhand >= 100 AND quantityonhand <= 200
• The word AND in BETWEEN is part of the syntax, not a separate logical operator.
• NOT IN / NOT BETWEEN exist as opposites.

Pattern matching with LIKE

• LIKE supports wildcards: % (any sequence) and _ (single character).
• Example: WHERE SKU_description LIKE '%dive%') finds any description containing the word dive.
• Example: WHERE SKU_description LIKE '%small%' finds any containing small.
• Wildcards vs DOS: % replaces the DOS asterisk; _ replaces single-character placeholder.
• Start/end patterns: LIKE 'D%' (starts with D); LIKE '%D' (ends with D).
• Exact length: '___' (three characters total).
• NOT LIKE negates the pattern.
• You can combine patterns with OR, e.g., (LIKE 'D%' OR LIKE 'M%').

NULL vs blank

• NULL means there is no data; different from a blank value ('').
• Check for missing data with: WHERE supervisor IS NULL.
• Example: Mary Smith may have no supervisor; result would include her row.
• WHERE supervisor = '' does not detect NULL; NULL is not equal to blank.

Aggregates and AS alias

• Aggregates: AVG, SUM, COUNT, etc. They summarize data and return a single value per query (or per group).
• A query using an aggregate can produce only one row for the aggregate results unless grouped.
• You can include multiple aggregates in the same SELECT.
• Rule: you cannot mix non-aggregated columns with aggregated expressions in the same SELECT without grouping.
• Aliases with AS improve readability; e.g., SUM(quantityonhand) AS avgquantityonhand; AVG(quantityonorder) AS avgquantityonorder.
• For spaces in aliases, use brackets [like this] or, in some systems, quotes; AS is only for readability, not required for engine logic.
• Example: SELECT SUM(quantityonhand) AS [avg quantity on hand], AVG(quantityonorder) AS [avg quantity on order] FROM inventory;
• Common shorthand: you can have multiple aggregates, but non-aggregated columns must be grouped by (GROUP BY) to avoid collisions.
• The line output idea: aggregates may take many rows and return a single summary value (one line).

NOT operator and grouping reminders

• NOT inverts conditions: NOT IN, NOT BETWEEN, NOT LIKE, NOT NULL.
• IS NOT NULL is the natural way to express a non-NULL check in many SQL dialects.
• Page 88 introduces grouping/aggregate concepts; grouping by non-aggregated columns allows mixed displays of aggregates and details.