By the end of this lesson you can write a SELECT … FROM query that returns chosen columns from a table, and explain each part of it.
Why it matters: Every report, dashboard and data question starts with retrieving the right columns from the right table. SELECT is the statement you will write more than any other.
Dataset: Starter shop. Examples run in the editor on the right.
So far you've looked at whole tables with SELECT *, and at their structure. Real questions rarely need every column, so this lesson is about asking for exactly the columns you want.
A query is a question you ask the database, written in SQL. The database reads it, finds the data, and answers with a result: a new table of rows and columns, built just for your question. Nothing in the stored data changes when you run a query. You can run the same query as many times as you like.
The simplest useful question is “show me these columns from that table”. In SQL it has two parts:
SELECTlists the columns you want, separated by commas.FROMnames the table they come from.
SELECT column_one, column_two
FROM table_name;SELECT customer_name, city
FROM customers;PostgreSQL goes to the customers table and, for every row, keeps only the customer_name and city columns. The result has 12 rows (one per customer) and 2 columns, in the order you listed them.
Look carefully at the result. One customer has an empty-looking city: the result grid shows NULL. NULL means no value was recorded: it is not the text 'NULL', and it is not an empty string. You'll learn how to work with NULL properly in the next track; for now, notice that the database shows you exactly what is stored, including what's missing.
The order of columns in the result follows the order in your SELECT list, not the order in the table. Writing SELECT city, customer_name puts the city first.
Selecting every column
An asterisk * means all columns, in table order. It's handy when you are exploring a table for the first time. In real reports, prefer naming the columns: the query documents itself, returns only what's needed, and keeps working the same way if someone adds a column to the table later.
SELECT *
FROM products;Returns all five columns of the products table for all ten products. Use this to get a feel for a table, then switch to naming the columns you actually need.
How to read an error
If you misspell a column, PostgreSQL refuses to run the query and tells you why, for example column "cty" does not exist. That isn't a failure on your part. It's the database being precise. Read the message, fix the name (the schema panel lists every column), and run it again. Professionals do this dozens of times a day.