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:
SQL1/* Using 'AND' operator */
2SELECT * FROM Matches WHERE competition_id > 1 AND date < '2006-01-01';
3
4/* Using 'OR' operator */
5SELECT * 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:
SQL1/* Using 'IN' operator */
2SELECT * FROM Matches WHERE match_id IN (1, 2, 3);
3
4/* Using 'BETWEEN' operator */
5SELECT * 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.
Finally, we have the NOT
operator, which is used to exclude records that meet specific conditions. Let's try it out in a SELECT
statement:
SQL1/* Using 'NOT' operator */
2SELECT * FROM Matches WHERE match_id NOT BETWEEN 3 AND 8;
In the snippet above, we use the NOT
operator to exclude certain records from our selection. This line extracts features of matches where the match_id
is not in the range of 3 to 8.
Logical operators, such as AND
, OR
, IN
, NOT IN
, and BETWEEN
, help us refine our SQL queries to get the data we need in a more efficient and specific way. These tools will allow us to retrieve specific sets of data from our database, which in our case, are Messi's football stats.
Great job! Today, you've deepened your understanding of the SELECT
statement through the usage of logical operators, making your data retrieval more precise and flexible. You started with AND
and OR
operators, moved to the IN
operator, and finally used the BETWEEN
operator.
As usual, remember that practice makes perfect! Our upcoming exercises will offer hands-on learning opportunities to further enhance your skills and understanding. We'll be using Messi's soccer stats to run some SQL queries using all the logical operators you've learned.
So, get ready to retrieve some specific data while deepening your understanding of SQL. Keep practicing, and you'll become a SQL superstar in no time!