The SQL "SELECT" statement is the expression used to retrieve data from the database. You can retrieve data from just one table or several. To get started, it's best to retrieve data from one table and learn how to join tables later. Note that most SQL developers use all caps for SQL expressions. 

The SELECT Statement

The following is a template you can use to write a SELECT statement: 

SELECT <columns> FROM <table> WHERE <where_clause> 

First, the SELECT statement lets the SQL processor know that you want to search and retrieve data from your database. The "<columns>" section is dynamic and must be names that match your table. You determine the columns you want to return. You can use the asterisk character ( * ) to indicate that you want to return all columns, but good SQL programming doesn't use the asterisk in SELECT statements. Returning all columns degrades performance on your SQL database, and it can be a security issue since a return of all columns gives the hacker a complete structure of your tables. 

The "FROM <table>" tells SQL where to get the columns and data. You must have FROM in your SELECT statement to let SQL know where it retrieves data. The "<table>" phrase is dynamic and depends on the table you want to query. For instance, if you wanted to get a list of customers, you would exchange "<table>" for "Customers." 

The only requirements for a SELECT statement are the columns and table name. The WHERE clause is optional, but you need the WHERE clause in most cases. When you exclude the WHERE clause, you return all records in the table. For instance, the following SELECT statement selects all records from the Customers table: 

SELECT first_name, last_name FROM Customers 

The following data set is an example of what SQL returns. 

Customer

First_name	Last_name
Frank	Loe
Ed	Thompson
Ed	Thompson
Joe	Smith
Frank	Doe

In most cases, you don't want to return all records. In the above example, SQL returns the first name and last name for all customers in the "Customers" table. Usually, you want to select data from only a specific number of records. For instance, you might want to find all customers with the last name of "smith." Note that case does not matter. "Smith," "SMITH," and "smith" will return the same records. The following SQL statement finds all customers with the last name of "smith" and returns them: 

SELECT first_name, last_name FROM Customers WHERE last_name='smith' 

The below data set is what SQL returns. 

First_name	Last_name
Joe	Smith

You could also use the following SQL statement to return all columns: 

SELECT * FROM Customers WHERE last_name='smith' 

You would use the second statement when you want to review all columns, but again, for performance reasons it's best to avoid using the asterisk to return columns. Using the asterisk is beneficial if you want to review data, but you don't know column names in your table. Notice that the string value is placed in single quotes. In most programming languages, you use quotes to indicate string input. 

When you create your SELECT statement, you need to know the columns in your table. If you use a column name that doesn't exist in the table, your SQL engine will return an error. 

Let's say you want to search your records for one customer, you use the same SELECT query but using the unique Id. The following image is an example of a SQL table: 

The above table is similar to customers, except it's named "Accounts." The AccountId column is set as the primary key, so you know it's the column that must be unique from the rest of the table data. You can then use this column to find one customer. When you work with websites, you normally have a table that holds a list of accounts or customers. You'll need to find one customer out of thousands (or millions) of other customers. The primary key column is how you search your table for a customer. Using the above table, you can find a unique customer using the following SELECT query: 

SELECT first_name, last_name FROM accounts WHERE AccountId=344 

Notice the integer value does not have quotes surrounding it. You don't use quotes when you query by a numeric value. In the above query, the result is one record with the AccountId that matches 344. 

SQL also gives you the ability to use dates in your queries. For instance, you might want to get a list of customers who have signed up in the last 24 hours. In the Accounts table example, the column name is "CreateDate." The SQL language has a "getDate()" function that grabs the current date. The getDate() function gets the date on the server. If your server is in a different time zone than your own, the SQL language displays the server's time. The following code gets a list of accounts that signed up in the last 24 hours: 

SELECT first_name, last_name FROM accounts WHERE CreateDate > getdate()-24 

The getdate()-24 statement gets the current date and subtracts 24 hours from it. It then grabs data where the date is greater than the date logged in the CreateDate column. The result is a list of customers that signed up within the last 24 hours for your reports. 

The WHERE clause isn't limited to just one column. You can use the WHERE clause to filter on several columns.  For instance, suppose you don't know the customer's unique account Id. You know the customer's last name and you know that he signed up between two dates. The following query helps you find the customer: 

SELECT AccountId, FirstName, LastName FROM accounts WHERE LastName = ‘smith' and CreateDate between ‘1/1/2014' and ‘1/31/2014' 

In the example above, the SQL statement finds all customers with the last name of "smith" and limits the list even more to those customers that signed up between ‘1/1/2014' and ‘1/31/2014'. Dates, like strings, are also in single quotes. The example query doesn't limit your results to one record. There is a chance that the query could return more than one account, but the record set should be small enough where you can find the right customer and then use a SQL query search to find the exact customer. 

The DISTINCT keyword is also useful when you want to find one record in your table. DISTINCT returns only unique values. For instance, suppose you want to know what countries your customers are from. You probably have multiple customers in the same location, but you just want a list of cities. You use the DISTINCT phrase to return only unique cities in your customer table. The following statement gets a distinct list of cities: 

SELECT DISTINCT city FROM Accounts 

With the above query, even if you have dozens of customers in the same city, the city value is only returned once. You then have a list of customer cities. 

One issue with these queries is that they are unordered. It helps to return a record set that's alphabetically ordered. SQL has a phrase called ORDER BY that lets you alphabetically, numerically, or chronologically order your records. The following SQL statement orders records by city name: 

SELECT DISTINCT city FROM Accounts ORDER BY city 

You can also order records in the reverse. The following code alphabetically lists a distinct record set of cities in the reverse order: 

SELECT DISTINCT city FROM Accounts ORDER BY city DESC 

SQL lets you order records using multiple columns. Suppose you want to see a list of customers for each city. You want to order the cities but you also have multiple customers in the same city, so you want to alphabetize the customer names by last name. The following SQL statement does the sorting for you: 

SELECT city, firstname, lastname FROM Accounts ORDER BY city, lastname 

The DISTINCT phrase was taken out of the statement since you could have a customer in the same city with the same first and last name. Using the DISTINCT phrase would eliminate some customers, and your report would be wrong. The example statement returns a list of customer first and last names. The first part of the list displays the city, and cities are alphabetically ordered. After the cities are ordered, SQL then sorts customers within each city by the customer's last name. 

One last value you can store in your tables is NULL. NULL values are placeholders that indicate "no value," although NULL is technically a value. SQL table design lets you decide if you allow NULL values in your columns. When NULL is stored as the value, you might want to remove these values from your reports. For instance, your customer didn't enter a city in the sign-up process and you didn't require it, so the database stored NULL instead of a value. The following SQL statement filters out NULL values from the record set: 

SELECT city, firstname, lastname FROM Accounts WHERE city IS NOT NULL ORDER BY city, lastname.

"IS NOT NULL" returns only values that are not NULL and "IS NULL" returns values that are NULL. These two WHERE clause expressions are used frequently in SQL statements where the table designer stores NULL values. 

Whenever you want to retrieve data from your tables, SELECT is how you do it. This chapter's examples are basic query statements that will come in handy in future programming. SELECT statements can be several lines long and get complex with several columns in the WHERE clause. Once you know how to retrieve your data, you can manipulate and edit it.