Multiple Conditions

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
Example

SELECT *
FROM table_name
WHERE username = ‘rustyMeerkat’
AND password = ‘digholes’

Result
id username password birthday
2 rustyMeerkat digholes 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
Example

SELECT *
FROM table_name
WHERE username = ‘rustyMeerkat’
OR password = ‘secretP’

Result
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:

Example

SELECT * FROM someTable
WHERE column1 = “pickles” AND (column 2 = “possible value 1″ OR column 2 = ” possible value 2″)

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.

If you enjoyed this resource, support me by sharing this page with others.
Share on Facebook
Facebook
Share on Google+
Google+
Share on StumbleUpon
StumbleUpon
Digg this
Digg
Tweet about this on Twitter
Twitter
Share on Reddit
Reddit

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.