SELECT: reading data

SELECT is the statement you will write most; everything else in querying builds on its shape.

A query starts with SELECT, lists the columns you want, and names the table with FROM:

SELECT name, city FROM customers;

The result is itself a table: a set of rows with the columns you asked for. SELECT * returns every column, which is handy when exploring in a console but a bad habit in application code. It fetches data you do not use, breaks when someone adds or reorders columns, and hides from readers which columns the code actually depends on. Name the columns.

Some syntax rules that trip people up:

Keywords are case-insensitive. select and SELECT are the same; uppercase keywords are just a readability convention.
String literals use single quotes: 'London'. Double quotes are for identifiers such as "Order Total", a column name with a space or capital letters. MySQL accepts double-quoted strings by default; PostgreSQL and the standard do not.
Unquoted identifiers are folded to lower case in PostgreSQL, so SELECT Name FROM Customers works on a table created as customers.
A statement ends with ;. Consoles like psql need it; most client libraries do not.

PostgreSQL, MySQL, SQLite and SQL Server let you SELECT without a table, which is useful for quick calculations: SELECT 2 + 3;. Oracle requires FROM dual.

Although you write SELECT first, the database logically evaluates FROM first: it identifies the rows, then filters and groups them, and only then computes the select list. Keeping that order in mind explains many rules you will meet later.

Example

SELECT name, city FROM customers;
--  name  | city
--  Ana   | Yerevan
--  Ben   | London
--  Chloe | London
--  Dev   | NULL

SELECT id, name, price FROM products;
--  id | name     | price
--   1 | Keyboard |  49.00
--   2 | Mouse    |  19.50
--   3 | Monitor  | 189.00
--   4 | Ebook    |   9.99

SELECT 2 + 3 AS answer;   -- no table needed in PostgreSQL
--  answer
--       5

Remember

SELECT columns FROM table returns a new result table
Prefer explicit column lists to SELECT * in application code
Single quotes for strings, double quotes for identifiers

Watch out for

Writing "London" with double quotes in PostgreSQL: it is read as a column name and fails with 'column does not exist'.

Check yourself

In PostgreSQL, what does SELECT name FROM customers WHERE city = "London" do?
Why is SELECT * discouraged in application code?

Official docs: www.postgresql.org/docs/current/sql-select.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks