SQL WHERE

In the first chapter of our SQL tutorial I promised you to explain how to use the WHERE clause along with the SELECT SQL keyword, and now is the time to deliver on my promise. We'll be using the Cars table already familiar from the previous chapter to show you SQL WHERE in action. Consider the SQL statement below:

SELECT * FROM Cars WHERE Color = 'Silver';

The first 2 lines should be already familiar to you, and if not they simply instruct the SQL interpreter to retrieve all columns from table Cars. The 3rd line is new and contains the WHERE clause. The WHERE clause was designed to allow adding a search condition to a SQL statement. This is simply a way to limit/filter the data set we are working with. The actual search condition comes right after the WHERE keyword, and in the example above specifies that we want to retrieve only cars that are Silver in color. Here is the result, of the above WHERE clause:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Silver
Lexus	ES 350	2008	Silver

The WHERE clause condition can work with other operators besides =, for example >, <,> =, <=, IN, and BETWEEN. If you want to retrieve all cars manufactured later than 2005, you can use the following SQL statement:

SELECT * FROM Cars WHERE Year > 2005;

And here is what you get:

CarMake	Model	Year	Color
Lexus	ES 350	2008	Silver
BMW	3 Series Coupe	2009	NULL

When using SQL WHERE clause you are not limited to using only one search condition. If you want to combine the examples above and select all Silver cars, manufactured after 2005, you can do it with the following SQL expression:

SELECT * FROM Cars WHERE Color = 'Silver' AND Year > 2005;

We used the SQL keyword AND in the example above, which instructs the SQL interpreter to return rows that have both Silver in the Color column and a number greater than 2005 in the Year column. The result look like this:

CarMake	Model	Year	Color
Lexus	ES 350	2008	Silver

If you want to get all cars that are either Silver or are newer than 2005, then use the OR keyword instead of AND:

SELECT * FROM Cars WHERE Color = 'Silver' OR Year > 2005;

The above SQL statement returns:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Silver
Lexus	ES 350	2008	Silver
BMW	3 Series Coupe	2009	NULL