Created: 2022-07-29
Tags: #fleeting
Problem: If you had a table with a hundred million rows of data,
...reading through all rows to find the specific column you want to
...would be inefficient and perhaps even impossible.
Solution: We need to filter results
SELECT column, another_column, …
FROM mytable
WHERE condition
AND/OR another_condition
AND/OR …;
WHERE clause=, !=, <, <=, >, >=
WHERE col_name = 4
Means, if column name is equal to 4
BETWEEN AND ConstraintNumber is within range of two values (inclusive)
BETWEEN … AND …
WHERE col_name BETWEEN 1.5 AND 10.5
Where col_name's value is in between 1.5 and 10.5
IN ConstraintNumber exists in a list
IN (Numbers here)
WHERE col_name IN (2, 4, 6)
Where col_name's value is in the numbers 2, 4, 6
Strings also works
WHERE People.name in ("Johnny Depp", "Helena Bonham Carter");
LIKE ConstraintCase insensitive exact string comparison
LIKE
WHERE col_name LIKE "ABC"
% ConditionUsed anywhere in a string to match a sequence of zero or more characters
(only with LIKE or NOT LIKE)
LIKE "%AT%"
WHERE col_name LIKE "%TOY STORY%"
Results -> matches "Toy Story 3", "Toy Story", "Toy Story asdkjaslkd"
Full-text search is best left to libraries designed specifically to do full text search
Apache Lucene or Sphinx.
As a result are
-> more efficient
-> support a wider variety of search features
including internationalization and advanced queries.
_ ConditionUsed anywhere in a string to match a single character
(only with LIKE or NOT LIKE)
col_name LIKE "AN_"
Results -> matches "AND", but not "AN"
NOT ConstraintNumber is not within range of two values (inclusive)
NOT BETWEEN … AND …
WHERE col_name NOT BETWEEN 1 AND 10
Where col_name's value is not in between 1 and 10
Number does not exist in a list
NOT IN (…)
WHERE col_name NOT IN (1, 3, 5)
Case insensitive exact string inequality comparison
NOT LIKE
col_name NOT LIKE "ABCD"
IS NULL constraintTest a column for NULL or empty values
IS NULL or IS NOT NULL constraint.
Select query with constraints on NULL values.
SELECT column, another_column, … FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;