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:

SQL
Copy to clipboard
1/* Using 'AND' operator */
2SELECT * FROM Orders WHERE YEAR(order_date) > 2022 AND order_status = 'Delivered';
3
4/* Using 'OR' operator */
5SELECT * FROM OrderItems WHERE extended_support = 1;

Now let's analyze the above code snippets:

In the first example, we employ the AND operator, which will extract orders from the database (SELECT * FROM Orders) that meet both conditions - the order was made after 2022 and the order_status is 'Delivered'.

In the second snippet, an OR operator is not directly used, but it's implied as we filter the rows in OrderItems table where the extended_support field is true (1). This is a single-condition filter and doesn't showcase OR directly.

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:

SQL
Copy to clipboard
1/* Using 'IN' operator */
2SELECT * FROM Orders WHERE customer_id IN (1, 2);
3/* Same query using multiple 'OR' operators */
4SELECT * FROM Orders WHERE customer_id = 1 OR customer_id = 2;
5
6/* Using 'BETWEEN' operator */
7SELECT * FROM Orders WHERE order_date BETWEEN '2021-01-01' AND '2022-01-01';
8/* Excluding boundary values */
9SELECT * FROM Orders WHERE order_date > '2021-01-01' AND order_date < '2022-01-01';

The first example employs the IN operator to extract orders placed by customers with customer_id 1 or 2.

The BETWEEN operator in the third query performs a range-based search, extracting orders with an order date between '2021-01-01' and '2022-01-01', including both dates. If you don’t want the boundary values, refer to the last line where explicit conditions are used to exclude them.

Note: Modern SQL engines optimize IN and OR similarly, so there’s no significant difference in execution for small lists. However, for longer lists, IN is preferred for clarity.

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:

SQL
Copy to clipboard
1/* Using 'NOT' operator */
2SELECT * FROM Orders WHERE order_date NOT BETWEEN '2021-06-01' AND '2021-12-31';

In the snippet above, we use the NOT operator to exclude certain records from our selection. This line extracts orders where the order_date is not in the range from '2021-06-01' to '2021-12-31'.

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 this case concern online shopping data.

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 online shopping data 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!