SQL - Where

The WHERE clause sets a conditional statement, and it can be used with any type of SQL query. As the select query executes, SQL processes one row at a time. Each time the conditional statement is met (returns true), a row is returned as a result. SQL WHERE is essentially, a filtering mechanism for SQL queries and is a tremendous asset to any aspiring SQL developer.

SQL Where Query:

USE mydatabase;

SELECT *
FROM orders
WHERE customer = 'Tizag'

As we take a look at the results, notice how only the rows that meet the criteria (where the customer column value is Tizag) are returned. In this example, we are using the WHERE clause to filter out rows and only selecting data that meets the conditional statement.

SQL Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Pen	4
2	Tizag	2008-08-01 00:00:00.000	Stapler	1
5	Tizag	2008-07-25 00:00:00.000	19" LCD Screen	3
6	Tizag	2008-07-25 00:00:00.000	HP Printer	2

Conditional statements are not unique to SQL, and neither are operators. Operators are symbols such as (=) or (<), and they are seen inside of conditional statements and expressions in SQL and other programming languages. While we're not going to dive into much detail about the different kinds of operators yet, it is a good idea to be familiar with them and be able to recognize them inside of conditional statements as we look over the next few examples.

SQL - Where Queries

With the WHERE clause on our tool belts, we can be more creative when querying for table rows. For instance, there may come a time where we would like to take a look at all the orders placed after a certain date.

SQL Where Date Query:

USE mydatabase;

SELECT *
FROM orders
WHERE  day_of_order > '7/31/08'

This conditional statement will return only the orders that have made it into the table since the end of July, filtering out any orders in the table made prior to July 31^st.

SQL Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Pen	4
2	Tizag	2008-08-01 00:00:00.000	Stapler	1
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	12
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	3

Notice how the date value is formatted inside the conditional statement. We passed a value formatted MM/DD/YY, and we've completely neglected the hours, minutes, and seconds values, yet SQL is intelligent enough to understand this. Therefore, our query is successfully executed.

SQL - Where with Multiple Conditionals

A WHERE statement can accept multiple conditional statements. What this means is that we are able to select rows meeting two different conditions at the same time.
Perhaps the easiest way to go about this is to add another condition to the previous example, where we retrieved only the orders placed after July 31^st. We can take this example one step further and link two conditional statements together with "AND".

SQL Where And:

USE mydatabase;

SELECT *
FROM orders
WHERE  day_of_order > '7/31/08'
AND customer = 'Tizag'

At this point, we have sent SQL two conditional statements with a single WHERE clause, essentially applying two filters to the expected result set.

SQL Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Pen	4
2	Tizag	2008-08-01 00:00:00.000	Stapler	1

By applying the AND clause, SQL has now been asked to return only rows that meet both conditional statements. In this case, we would like to return all orders that were made before July 31^st and made by a specific company

Posted in: SQL