SQL - AND and OR Conjunctive Operators

Hello there, future SQL masters! Welcome to our lesson on AND and OR operators in SQL. I'm thrilled to guide you through this journey, and I promise, by the end of this tutorial, you'll be wielding these operators like a pro. So, let's dive in!

The SQL AND Operator

The AND operator is like the strict parent of logical operators. It only returns true when all conditions are met. Think of it as a picky eater who only enjoys a meal if every single ingredient is to their liking.

Let's look at a simple example:

SELECT * FROM Students
WHERE age > 18 AND grade = 'A';

This query is saying, "Show me all students who are over 18 AND have an A grade." It's like asking for the perfect student!

Here's a more complex example:

SELECT product_name, price, stock_quantity
FROM Products
WHERE price < 50 AND stock_quantity > 100 AND category = 'Electronics';

This query is looking for budget-friendly electronics that are well-stocked. It's like hunting for a good deal in a store!

Multiple AND Operators

You can chain multiple AND operators together, creating a very specific filter. It's like building a sandwich with many layers - each layer (condition) needs to be present for the query to return results.

SELECT * FROM Employees
WHERE department = 'Sales'
  AND years_of_experience > 5
  AND salary < 75000
  AND performance_rating = 'Excellent';

This query is searching for experienced, high-performing sales employees who might be due for a raise. It's like a boss looking for the perfect candidate for a promotion!

AND with Other Logical Operators

AND can be combined with other operators like OR and NOT to create complex conditions. Let's look at an example:

SELECT * FROM Cars
WHERE (make = 'Toyota' OR make = 'Honda')
  AND year > 2015
  AND (color = 'Red' OR color = 'Blue');

This query is looking for recent Toyota or Honda cars that are either red or blue. It's like a very particular car shopper!

AND with UPDATE Statement

AND is not just for SELECT statements. It's also useful in UPDATE statements when you want to modify specific records:

UPDATE Products
SET price = price * 1.1
WHERE category = 'Electronics'
  AND stock_quantity < 50
  AND last_order_date < '2023-01-01';

This query increases the price of electronics that are low in stock and haven't been ordered recently. It's like a store manager adjusting prices based on demand!

The SQL OR Operator

If AND is the strict parent, OR is the lenient one. It returns true if any of the conditions are met. It's like a food critic who's happy if just one dish in the meal is good.

Here's a simple example:

SELECT * FROM Customers
WHERE country = 'USA' OR country = 'Canada';

This query retrieves all customers from either the USA or Canada. It's casting a wider net than AND would.

Multiple OR Operators

Like AND, you can chain multiple OR conditions:

SELECT * FROM Books
WHERE genre = 'Science Fiction'
   OR genre = 'Fantasy'
   OR genre = 'Mystery'
   OR author = 'Stephen King';

This query is looking for books that fit into several popular genres, or are by a specific author. It's like browsing a bookstore's "Popular Picks" section!

OR with AND Operator

Combining OR and AND can create powerful queries. Remember to use parentheses to group your conditions correctly:

SELECT * FROM Restaurants
WHERE (cuisine = 'Italian' OR cuisine = 'French')
  AND price_range <= 3
  AND rating >= 4;

This query is looking for highly-rated, affordable Italian or French restaurants. It's perfect for planning a nice dinner out!

OR with DELETE Statement

OR can also be used in DELETE statements to remove records that meet any of several conditions:

DELETE FROM Inventory
WHERE (expiry_date < CURRENT_DATE)
   OR (stock_quantity = 0 AND last_sold_date < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH));

This query removes items that are either expired or have been out of stock for over six months. It's like a store manager cleaning up the inventory database!

Here's a table summarizing the key points about AND and OR operators:

Operator	Description	Use Case	Example
AND	Returns true when all conditions are true	Narrow down results	WHERE age > 18 AND grade = 'A'
OR	Returns true when any condition is true	Broaden search criteria	WHERE country = 'USA' OR country = 'Canada'

Remember, the key to mastering these operators is practice. Try writing your own queries, experiment with different combinations, and soon you'll be crafting complex SQL statements with ease!

I hope this tutorial has illuminated the world of AND and OR operators for you. Keep coding, keep learning, and most importantly, keep enjoying your SQL journey!

Credits: Image by storyset