SQL, how to use SELECT

You can get data out of tables using the SELECT command.

Get all rows and columns:

SELECT * FROM people;

 age |  name  
-----+--------
  37 | Flavio
   8 | Roger

* means “every column”. It’s handy while exploring, but in application code my advice is to list the columns you need. The query keeps returning the same shape when the table changes, and you don’t transfer data you never use.

Get only the name column:

SELECT name FROM people;

  name  
--------
 Flavio
 Roger

You can ask for several columns, separated by commas, and rename them in the output with AS:

SELECT name AS person, age FROM people;

The result columns follow the order you list them, not the order in the table definition.

Count the items in the table:

SELECT COUNT(*) from people;

 count 
-------
     2

By the way, once your tables grow, queries with WHERE clauses can get slow without the right index. I built a free index advisor that suggests the CREATE INDEX statement for the shape of your query.

You can filter rows in a table adding the WHERE clause:

SELECT age FROM people WHERE name='Flavio';

 age 
-----
  37

Notice the single quotes around 'Flavio'. In SQL, single quotes wrap string values. Double quotes wrap identifiers such as column names, so WHERE name="Flavio" fails in PostgreSQL with ERROR: column "Flavio" does not exist. MySQL is more forgiving here, but don’t rely on that.

The results of a query can be ordered by column value, ascending (the default) or descending, using ORDER BY:

SELECT * FROM people ORDER BY name;

SELECT * FROM people ORDER BY name DESC;

Without ORDER BY, the database returns rows in whatever order is convenient for it. If order matters to you, say so explicitly. Never assume insertion order.

One more common failure: misspelling a column name. SELECT nmae FROM people; stops with ERROR: column "nmae" does not exist. The database won’t guess what you meant. Check the table definition and run the query again.

Related posts about database: