Using Conditionals

The problem with the SELECT statement as used in the preceding section is that it will automatically retrieve every record. While this isn't a problem when dealing with a few rows of information, it will greatly hinder the performance of your database as the number of records grows. To improve the efficiency of your SELECT statements, there are different conditionals you can use in almost limitless combinations. These conditionals utilize the SQL term WHERE and are written much as you'd write a conditional in any programming language.

SELECT whatcolumns FROM tablename WHERE conditions

The conditions are normally a combination of column names, literal values (numbers or strings), and operators. Table 5.2 lists the most common operators you would use within a WHERE conditional. Example queries are:

SELECT expense_amount FROM expenses WHERE expense_amount <= 10.00 SELECT client_id FROM clients WHERE client_name = 'Acme Industries'

Table 5.2. These are the most common MySQL operators. Appendix B, "SQL & MySQL References," will contain the exhaustive list.

MySQL Operators
Operator	Meaning
=	equals
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to
!= (also <>)	not equal to
IS NOT NULL	has a value
IS NULL	does not have a value
IN	value found within a list
BETWEEN	within a range
NOT BETWEEN	outside of a range
OR (also ||)	where one of two conditionals is true
AND (also &&)	where both conditionals are true
NOT (also !)	where the condition is not true

These operators can be used together, along with parentheses, to create more complex conditionals.

[View full width]
SELECT expense_amount FROM expenses WHERE (expense_amount >= 10.00) AND (expense_amount <=  20.00) SELECT client_id FROM clients WHERE (client_name = #Acme Industries$) OR (client_name =  #Something Clever$)

To demonstrate using conditionals, I'll retrieve more specific data from the accounting database. The examples that follow will show just a few of the possibilities. Over the course of this chapter and the entire book you will see any number of variants on SELECT conditionals.

To select particular data from a table:

1.	Select the expense_description for every Books expense type (Figure 5.8). SELECT expense_description FROM expenses WHERE expense_category_id = 3; Since I know that Books in the expense_categories table has an expense_category_id of 3, I can create this SQL query. It will return the expense_description for each record that has an expense_category_id foreign key of 3.Note that numbers should not be placed within quotation marks in your conditionals (or anywhere else in your query). Figure 5.8. The conditional in this query uses the expenses table's expense_category_id foreign key to select a particular record. If you did not enter a Books expense type, change your query accordingly.
2.	Select the invoice ID, amount, and date of every invoice entered since March 1, 2006 (Figure 5.9). [View full width] SELECT invoice_id, invoice_amount, invoice_date FROM invoices WHERE invoice_date <= #2006-03-01$; You can perform greater than and less than (or greater than or equal to, less than or equal to) calculations using dates, as I've done here. Figure 5.9. Date fields are very flexible in how you access the information, including being able to relatively select dates.
3.	Select everything from every record in the expenses table that does not have a date (Figure 5.10).

SELECT * FROM expenses WHERE expense_date IS NULL;

Figure 5.10. Because NULL is a special value in databases, the IS NULL and IS NOT NULL operators are used for these cases. No records were returned because every expense has an expense_date value.

The IS NULL conditional is the same as saying "does not have a value." Keep in mind that an empty string is the same thing as a value, in NULL terms, and therefore would not match this condition. Such a case would match

SELECT * FROM expenses WHERE expense_date = ";;

Tips

• Strange as it may seem, you do not have to select a column on which you are performing a WHERE.

  SELECT invoice_id FROM invoices WHERE client_id = 4

  The reason for this is that the columns listed after SELECT only determine what columns to return. Conversely, a WHERE clause determines which rows to return.

• You can perform mathematical calculations within your queries using the numeric addition (+), subtraction (-), multiplication (*), and division (/) characters.

• It's important that you never try to compare a NULL value to anything, as NULL is special and the results you'll see can be esoteric. For example, the conditionals NULL > 1, NULL < 1 and NULL = 1 all have the same result. For NULL comparisons, always use IS NULL and IS NOT NULL instead.