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 Orders WHERE YEAR(order_date) > 2022 AND order_status = 'Delivered';

/* Using 'OR' operator */
SELECT * FROM Orders WHERE order_status = 'Pending' OR order_status = 'Shipped';

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 example, we use the OR operator to extract orders where the order_status is either 'Pending' or 'Shipped'. Since only one of the conditions needs to be true, this query returns a broader set of results.

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 Orders WHERE customer_id IN (1, 2);
/* Same query using multiple 'OR' operators */
SELECT * FROM Orders WHERE customer_id = 1 OR customer_id = 2;

/* Using 'BETWEEN' operator */
SELECT * FROM Orders WHERE order_date BETWEEN '2021-01-01' AND '2022-01-01';
/* Excluding boundary values */
SELECT * 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.