SQL Overview

The SELECT command in SQL provides a robust means for retrieving data from a database. More specifically, SELECT returns a result set of zero or more rows from the database, and this result set corresponds to the query that was executed. The result set is calculated by the query optimizer inside the database system, based on the information inside the database. SELECT operates on base tables, temporary tables, functions, and views in a database.

Basic SQL SELECT Queries

The fundamental idea behind a SELECT query is to ask the database to show the user a set of data that fits certain criteria.

Figure 1 shows a SELECT query that asks the database to return books written by Dan Brown. This example displays every fundamental feature of the SELECT command. We can dissect this query and figure out how the database interpreted it.

The very first part of the query, SELECT *, instructs the database that the user wants to view all the columns associated with the resulting rows. This is why the result set contains the date, title, and author columns, even though they were not specified in the query.

After SELECT * comes FROM books, which tells the database the name of the table in which it should try to find the data. books is the name of a table in this database.

The WHERE author = ‘Brown, Dan’ clause lets the database know the user only wants to see books written by Dan Brown.

Two rows matched the requirements of the query, and they were returned to the user in a result set after less than a millisecond of processing.

The output order of the rows and columns in the result set is not necessarily deterministic; that is, SELECT * FROM books could output the rows and columns in any order. In PostgreSQL, the d [table_name] command will show the default column order, but it should not be considered reliable. Other database systems have similar commands.

Figure 2 shows that the order of the columns in the output can be altered by explicitly specifying the order. This only affects the output and does not change the order of the columns as they are stored in the database.

Figure 3 shows another similar query in which the date column is omitted from the result set.

Another basic but important function of SELECT is sorting the result set. This is accomplished by using the ORDER BY keyword. In most SQL databases, the default behavior is to sort in ascending order, but this can be explicitly set in the SELECT command with the ASC and DESC keywords. These two sorting methods can be seen in Figure 4.

Next Page: Advanced SQL SELECT Queries