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!