Into Statement  «Prev  Next»
Lesson 2 Filtering the SELECT Statement
ObjectiveLearn how to select Specific Columns in your Statements

Filtering the SELECT Statement

The first way to filter your SELECT statement is to indicate a specific series of columns to be returned as the results to your query. You do this by indicating the columns you want as part of the SELECT statement. Consider the following two examples:

SELECT * FROM Customer
SELECT Lastname, Firstname FROM Customer 

In the first statement, you select all columns from the table, which is what you have been doing up to this point in this course.
In the second statement, you will receive only two columns, the Lastname and Firstname columns, in the results set returned by your query. This is more precise. As a rule, if you find yourself manipulating information in the database before you can use it, see if you can move more precise statements and clauses into your SQL.
Here's a jargon term that you may hear referenced for this approach: vertical partitioning. All it means is that you are making a vertical cut at the database by indicating the columns you want to be returned.

The most commonly used SQL keyword, and the only one that reads data from a database is the SELECT keyword. I will explain some basic elements of style and syntax, and the features of the
  1. WHERE clause,
  2. Boolean operators, and
  3. sorting.

Single Table SELECTs

The most basic form of SELECT reads the data in all rows and columns from a table.
In the following example, you will start the monitor and choose the music database:

mysql> use music;
Database changed
We will now retrieve all of the data in the artist table:
mysql> SELECT * FROM artist;
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 1 | New Order |
| 2 | Nick Cave & The Bad Seeds |
| 3 | Miles Davis |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
| 6 | Kylie Minogue |
+-----------+---------------------------+
6 rows in set (0.08 sec)

The output has six rows, and each row contains the values for the artist_id and artist_name columns. We now know that there are six artists in our database and can see the names and identifiers for these artists.
In the next lesson, we will look at a second way to filter information when using the SELECT statement.