Mastering SELECT Statements with Logical Operators

Great job on making it this far! Today we're going to extend your SQL knowledge even further. So far, you've learned about the basics of SQL, the SELECT statement, and the WHERE clause. Today, we're going to focus on enhancing those skills using logical operators.

Logical Operators are at the heart of any computational language, SQL being no exception. They're used in the WHERE clause of SELECT statements (as well as other statements like INSERT, UPDATE, and DELETE which you'll learn about in the future) to combine or negate conditions and ultimately help us sieve out precise information from our database.

Firstly, we have the AND and OR operators.

An AND operator returns TRUE if both listed conditions are true. It essentially narrows your search results because it adds more conditions that records must meet.

Meanwhile, an OR operator returns TRUE if either of the conditions listed is true, effectively broadening your search results because it only requires one of the conditions to be met.

To see them in action, follow the code examples:

/* Using 'AND' operator */
SELECT * FROM Matches WHERE competition_id > 1 AND date < '2006-01-01';

/* Using 'OR' operator */
SELECT * FROM Matches WHERE venue = 'H' OR result = '5:0';

Now let's analyze the above code snippets:

In the first example, we employ the AND operator, which will extract matches from the database (SELECT * FROM Matches) that meet both conditions - the competition_id is greater than 1 and the date is earlier than 2006-01-01.

In the second snippet, we utilize an OR operator, extracting matches that are either played at Home venue (venue = 'H') or the result is 5:0 (result = '5:0'). This means matches that fulfill either or both conditions will be returned.

Next, we have the IN and BETWEEN operators:

The IN operator allows us to specify multiple values in a WHERE clause, a clean, efficient alternative to multiple OR conditions.

The BETWEEN operator selects values within a given range, which can be numbers, text, or dates.

Now let's use these operators:

/* Using 'IN' operator */
SELECT * FROM Matches WHERE match_id IN (1, 2, 3);

/* Using 'BETWEEN' operator */
SELECT * FROM Matches WHERE match_id BETWEEN 1 AND 5;

The first example employs the IN operator to extract (SELECT * FROM Matches) matches that have a match_id of 1, 2, or 3. It's less tedious than writing match_id = 1 OR match_id = 2 OR match_id = 3.

In the next line, the BETWEEN operator performs a range-based search. So, this command will extract matches that have a match_id between 1 and 5, i.e., numbers 1, 2, 3, 4, and 5.