SQL Diary 02

March 7, 2022

The Basics (part 3)

BETWEEN

The BETWEEN operator will match a value comparing it to a range of values. Example, is the value 6 between 9 and 3?

SELECT values FROM natural_numbers
WHERE values BETWEEN 3 AND 9;

This operator can also be used with dates^{1<\sup> and
with the NOT^{2<\sup> logical operator:}}

SELECT values FROM natural_numbers
WHERE values NOT BETWEEN 3 AND 9;

SELECT * FROM calendar
WHERE dates
BETWEEN '2001-02-01' AND '2001-02-28'

IN

The IN command will search for results that match with the items of an specified group of values. So it tells your query to look for items that are equal to the ones INside that group.

SELECT COUNT(*) FROM agents
WHERE last_update IN("Buff", "Fix")

LIKE and ILIKE

The commands LIKE and ILIKE allow us to perform patter matching against string data, usilg wildcard characters such as:

%: match any sequence _: match any single character

Examples, matching names that begin with ‘L’ and names that end with an ‘L’

WHERE name LIKE 'L%'
WHERE name LIKE '%l'

LIKE is case sensitive, an alternative to this would be ILIKE, which is case-insensitive.

Example: matching Harry Potter films (with number), Shrek films, version of a program (e.g 1.29, A.41)

WHERE title LIKE 'Harry Potter _'
WHERE title LIKE 'Shrek _'
WHERE version ILIKE 'Fix _.__'

“footnotes”

^{1<\sup> The end date (date used after
the AND operator) will be considered only up to 00:00,
so you could consider it exclusive on this kind
of query.}

^{2<\sup> The NOT BETWEEN combination
is not inclusive of the edge values.}