Lesson 6
When querying a database table, specifying constraints and conditions allows you to filter the result set to contain only relevant rows that meet defined criteria. This improves query performance, reduces network traffic and enables more useful analysis of the data.
In SQL, the WHERE clause is used to apply filters that constrain results to rows fulfilling your conditional logic.
The basic syntax for a constrained SELECT query is:
SELECT column1, column2,...
FROM mytable
WHERE condition1 AND/OR condition2
Numeric conditions leverage comparison operators like:
For example:
SELECT name, price
FROM products
WHERE price <= 100
Retrieves rows for products priced 100 or under. Multiple AND/OR conditions can be chained to further filter:
SELECT name, stock
FROM products
WHERE price < 50 AND stock > 10
Now only products under $50 with over 10 units in stock are returned.
SQL offers additional operators for more advanced conditional logic:
BETWEEN provides numeric range filtering:
SELECT name, qty
FROM sales
WHERE qty BETWEEN 10 AND 50
NOT BETWEEN filters excluding a range.
IN and NOT IN check if values match any element in a set:
SELECT name
FROM products
WHERE category IN ('Appliances', 'Electronics')
For text, LIKE and NOT LIKE enable partial string matching using wildcards:
SELECT title
FROM books
WHERE title LIKE '%Cook%'
The % matches any # characters. _ matches a single character.
You can chain numerous AND/OR operators to conjure complex conditional logic:
SELECT *
FROM data
WHERE
name LIKE 'A%' AND
fruit IN ('Apple', 'Apricot') OR
qty > 10 AND
purchase_date BETWEEN '2020-01-01' AND '2020-12-31'
This returns rows for names starting with A that have Apple/Apricot for fruit OR have over 10 quantity AND were purchased last year.
Carefully constraining result sets by leveraging comparison operators and condition chaining helps optimize SQL query performance and analytical value.
| Operator | Condition | SQL Example |
|---|---|---|
| =, !=, < <=, >, >= | Standard numerical operators | col_name != 4 |
| BETWEEN ... AND ... | Number is within range of two values (inclusive) | col_name BETWEEN 1.5 AND 10.5 |
| NOT BETWEEN ... AND ... | Number is not within range of two values (inclusive) | col_name NOT BETWEEN 1 AND 10 |
| IN (...) | Number exists in a list | col_name IN (2, 4, 6) |
| NOT IN (...) | Number does not exist in a list | col_name NOT IN (1, 3, 5) |
| = | Case sensitive exact string comparison (notice the single equals) | col_name = "abc" |
| != or <> | Case sensitive exact string inequality comparison | col_name != "abcd" |
| LIKE | Case insensitive exact string comparison | col_name LIKE "ABC" |
| NOT LIKE | Case insensitive exact string inequality comparison | col_name NOT LIKE "ABCD" |
| % | Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) | col_name LIKE "%AT%" |
| _ | Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) | col_name LIKE "AN_" |
| IN (...) | String exists in a list | col_name IN ("A", "B", "C") |
| NOT IN (...) | String does not exist in a list | col_name NOT IN ("D", "E", "F") |