Let’s quickly go over how queries are structured.
Consider the following table, pokemon:
To return all the data in this table, you’d use:
The query above:
- SELECTs,
- all the columns (represented by the * symbol),
- in the order of their definition in the database,
- FROM the pokemon table.
The returned table will contain all of the columns and all of the rows from the table.
All the SQL keywords like SELECT and FROM are case insensitive, so the code below also works.
The keywords are usually capitalized simply to make the code easier to read.
Selecting Columns
Queries in SQL are usually written to return only the columns you need.
To see some of the columns in a table, use a comma-separated list of the columns you need, after SELECT:
The query above gives the name, hp and attack information for all of the rows in the pokemon table. It results in:
If you want to try it out yourself, here’s the playground link.
Running a query gives a copy of your original table that contains only the requested columns. This resulting table is often called the *result set*.
The result set isn’t stored permanently in the database, and it doesn’t change any tables in the database.
Returning all data from a table is often useful to inspect new databases. But it’s sometimes a bad idea to use the SELECT syntax for real production data (which is often a lot of data). A couple reasons:
- Unnecessary costs! Some databases charge money per retrieved data (e.g. Google’s BigQuery)
- When working with multiple tables it’s possible to return two columns of the same name from two different tables (often leading to crashes!).
