Retrieve Data using SELECT¶

We already have a database and a table with some data in it that looks more or less like this:

id	root	nicodemus	english	originator
1	a	a	hello.	Lawrence Nicodemus
2	bc	buts	boots.	Lawrence Nicodemus
3	bs	tbilso	dragonfly.	null

Things are getting more exciting! Let's now see how to retrieve data from the database! 🤩

In SQL, the SELECT command is used to find and retrieve information from your database.

The SELECT statement has many components, and can actually be quite complex. Let's start simply. How about we just list out everything in the roots table.

SELECT id, root, nicodemus, english, originator 
FROM roots;

It means, more or less:

Hey SQL database 👋, please go and SELECT some data for me.

I'll first tell you the columns I want:

id

root

nicodemus

english

originator

And I want you to get them FROM the table called "roots".

Then the database will go and get the data and return it to you in a table like this:

id	root	nicodemus	english	originator
1	a	a	hello.	Lawrence Nicodemus
2	bc	buts	boots.	Lawrence Nicodemus
3	bs	tbilso	dragonfly.	null

You can try that out in DB Browser for SQLite:

DB Browser screenshot showing the result of running a SQL `SELECT` statement against the `roots` table.

Warning

Here we are getting all the rows.

If you have thousands of rows, that could be expensive to compute for the database.

You would normally want to filter the rows to receive only the ones you want. But we'll learn about that later.

A SQL Shortcut¶

If we want to get all the columns like in this case above, in SQL there's a shortcut, instead of specifying each of the column names we could write a *:

SELECT *
FROM roots

That would end up in the same result.

`SELECT` Fewer Columns¶

We can also SELECT fewer columns, for example:

SELECT id, nicodemus
FROM roots

Here we are only selecting the id and nicodemus columns.

And it would result in a table like this:

id	nicodemus
1	a
2	buts
3	tbilso

And here is something interesting to notice. SQL databases store their data in tables. And they also always communicate their results in tables.

`SELECT` Variants¶

The SQL language allows several variations in several places.

One of those variations is that in SELECT statements you can use the names of the columns directly, or you can prefix them with the name of the table and a dot.

For example, the same SQL code above could be written as:

SELECT roots.id, roots.root, roots.nicodemus, roots.english, roots.originator
FROM roots

This will be particularly important later when working with multiple tables at the same time that could have the same name for some columns.

For example roots.id and users.id.

Another variation is that most of the SQL keywords like SELECT can also be written in lowercase, like select.

Result Tables Don't Have to Exist¶

This is the interesting part. The tables returned by SQL databases don't have to exist in the database as independent tables. 🧙

For example, in our database, we only have one table that has all the columns: id, root, nicodemus, english, and originator. And here we are getting a result table with fewer columns.

One of the main points of SQL is to be able to keep the data structured in different tables, without repeating data, etc, and then query the database in many ways and get many different tables as a result.

What's Next¶

Let's learn about one very useful way to limit the results you get from a SELECT statement: the SQL WHERE clause. Let's go. 🚀