Select data from Table

Selecting all data

If you don't already have a database named "library", then make sure to run createdb library from the terminal before starting this chapter. The library database is needed to work through this chapter's content. The library database can be restored from a backup file. First download the backup file for this chapter from here.

Then run the following command: psql -d library < select_data.sql

The SELECT command lets us access data in a table. It is the most frequently used command by SQL developers, since developers are usually writing queries that show data to the user or provide data for an API endpoint. The command below performs a query on the users table and displays all the contents of that table. Now, we can see data we inserted in the last chapter.

library=# SELECT * FROM users;

When the above command is entered into the psql console we get the following back.

id |         username          | enabled
---+---------------------------+---------
20 | John Smith                | f
 1 | John Smith                | t
 2 | Harry Potter              | f
(3 rows)

Translated into plain English, the query we entered can be read as:

"Show me all the columns for all users in the table."

  • SELECT: The SQL command to retrieve data.

  • *: A wild card character is an indicator to the SELECT command to return all the columns it finds in a given table(s).

  • FROM: The FROM Clause tells the SELECT statement which database table will be used.

  • users: This is the table from which data is retrieved.

Selecting a column

We don't need to see all of the columns all of the time. For example, when we are writing code in production we may not even want to return the id of the record or some other columns. Also, returning all of the columns takes a longer time than returning just the columns we need.

If we only want to see the usernames, we could issue the command below:

library=# SELECT username FROM users;
username
---------------------------
John Smith
John Smith
Harry Potter
(3 rows)

You can read that command as "Show me the username for all users in the table."

Selecting multiple columns

You can also return multiple columns from a table, by simply separating the column names by a comma in the SQL statement.

library=# SELECT username,id FROM users;
username                  | id
--------------------------+----
John Smith                | 20
John Smith                |  1
Harry Potter              |  2
(3 rows)

This would translate in plain English to:

"Show me the username and id of all users."

Adding Criteria

The problem with the previous examples is that while we can select individual columns, the queries return all the rows in the table, something we might not want. This is where selection criteria come in. Since our table contains an enabled column, a useful query might be one that fetches only the enabled users. In SQL this would be written as:

SELECT username FROM users WHERE enabled = true;

Translated into English this would read as:

"Show me the usernames of all the users who have the column named enabled set to true." The diagram below explains in depth how the Select SQL statement works:

library=# SELECT username FROM users WHERE enabled = true;
         username
---------------------------
 John Smith
(1 row)

Use of = sign in SQL

In the WHERE clause of SQL queries, = is treated as an 'equality' operator, in that it compares things. In this case, it's checking whether the column 'enabled' is equal to the value 'true'. In most programming languages, a single = operator is used for variable assignment, but not so in SQL.

Ordering the data

SQL allows returning sorted data by adding the ORDER BY [column name] clause to a query. By default, the sorting order is ascending. From the results below you can see that the usernames are in alphabetical order.

library=# SELECT username, id FROM users ORDER BY username;
         username          | id
---------------------------+----
 Harry Potter              |  2
 John Smith                | 20
 John Smith                |  1
(3 rows)

Sort by ASC or DESC.

You can further sort the ORDER BY clause in either ascending or descending order, with the words ASC or DESC.

For example, to sort from larger to smaller values, you can use:
ORDER BY [field name] DESC

Here's how to use it in context of a complete query:

library=# SELECT username, id FROM users ORDER BY id DESC;
         username          | id
---------------------------+----
 John Smith                | 20
 Harry Potter              |  2
 John Smith                |  1
(3 rows)

Conversely, you can go from smaller to larger values with: ORDER BY [field name] ASC

Example:

library=# SELECT username, id FROM users ORDER BY id ASC;
         username          | id
---------------------------+----
 John Smith                |  1
 Harry Potter              |  2
 John Smith                | 20
(3 rows)

That's it for querying the data for now.

Now, suppose we need to modify data in the table. For example, how do we

  1. change the value of someone's username?
  2. fix a typo?
  3. update the enabled column for a specific user?
  4. delete an incorrect entry?

In the next chapter we will go over UPDATE and DELETE commands that will let us modify existing data in a table.

Summary

Let's quickly recap again:

Command Notes
SELECT * FROM users Select all columns from table users.
SELECT username,id FROM users ORDER BY id ASC Select the columns username, id from table users and sort the result in ascending order based on the id.
SELECT username FROM users WHERE enabled = true Select the column username which is enabled.