Enough of these simple short and sweet SQL Statements. It’s time to discover how to implement multiple conditions by using AND and OR in our queries. AND and OR are used in a very large amount of statements, especially user authentication. We have to check a username and a password. If you are familiar with programming logic, this tutorial will be a piece of cake for you. If not, it’s alright I will go into enough detail for you to understand.
- AND – both conditions must be true
- OR – either one of the conditions must be true
Using AND in SQL Statements
Our table before:
| id | username | password | birthday |
|---|---|---|---|
| 1 | bobdole32 | secretP | 1984-06-01 |
| 2 | rustyMeerkat | digholes | 1995-09-15 |
| 3 | rustyMeerkat | copyCat | 1995-09-15 |
A standard SELECT is used until we reach the AND. We would have expected it to returned both records with ‘rustyMeerkat’ as the username. However, the AND says, “Hey, hold up. I have something else.” SQL then realizes, we have another condition and checks it. The aha moment occurs, and we return one record that satisfies both of the conditions. Make sure that you are certain when you use an AND statement. Nothing is more frustrating in SQL than having a large query that returns nothing when you run it. You just scratch your head and slowly raising your voice at SQL.
Using OR in SQL Statements
Our new table:
| id | username | password | birthday |
|---|---|---|---|
| 1 | bobdole32 | secretP | 1984-06-01 |
| 2 | rustyMeerkat | digholes | 1995-09-15 |
The SQL keyword OR is considerably different than AND because OR loves everyone while AND is a jerk. We still have two conditions, but this time we want the records that have a username of ‘rustyMeerkat’ or a password = ‘secretP’, which, in this case, is both records. Get comfortable with these two commands. You will use them with a fair chunk of the SQL you will be writing. It is also important to know that you can use them inside parentheses. That gives you even more control over your WHERE statement. Let’s take a look at what I am talking about:
We already understand the column1 = “pickles”, but the AND with parentheses behind it is new to us. In the parentheses, we have 2 conditions separated by an OR statement. If either one of these are true, the condition after the AND statement will return true. So, we can still have the strict nature of AND, but we can provide options with OR to make our SQL query a little more robust.