Every question you will ever ask a database starts with the same word. SELECT reads rows from tables and returns a result — itself a small table of rows and columns. This lesson writes the simplest useful queries: picking columns, taking all of them, computing a value with no table at all, and asking for a dependable order. Everything else in SQL is added on top of this shape.

The shape of a SELECT

SELECT name, city
FROM students;

Two clauses: SELECT lists what to return — here two columns — and FROM names where to read it. The result has exactly the columns you listed, in the order you listed them, and one row for each row of the table:

The columns are separated by commas, and a missing or extra comma is the commonest first error:

SELECT name city FROM students;    -- no comma: one column "name", renamed to "city"!
SELECT name, city, FROM students;  -- trailing comma: syntax error

The first is worse than an error: it runs. A name after an expression with no comma is read as an alias (the next module explains them), so the result has one column called city holding names.

Picking and ordering columns

You may list any of the table's columns, in any order, and the same column more than once:

SELECT city, name FROM students;
SELECT name, name, year FROM students;

The table's own column order is irrelevant to the result. A table with forty columns is routinely queried for three of them — and reading fewer columns is less work for the database and the network.

SELECT *

The star means every column, in the table's order:

SELECT * FROM students;

Use it to explore a table you do not know. Avoid it in code that other code depends on:

• it reads columns nobody uses, which costs I/O and network, and prevents some index-only plans;
• when a column is added, dropped or reordered, the result changes shape and code that reads columns by position breaks;
• it hides intent — a reviewer cannot see which data the query needs.

Interviewers like asking "what is wrong with SELECT *?" — those three points are the answer.

Ordering the rows

A table is a set; it has no row order. If you run SELECT * FROM students twice you will probably see the same order, because the engine happens to read the rows the same way — but nothing promises it, and after a few deletes and inserts, or with a new index, the order changes. When order matters, say so:

SELECT name, year FROM students ORDER BY name;

ORDER BY comes after FROM (and after WHERE, once there is one). It sorts ascending by default — numbers small to large, text A to Z, dates old to new; add DESC for descending:

SELECT name, year FROM students ORDER BY year DESC;

You may order by a column you did not select, and by several columns — the second breaks ties in the first:

SELECT name FROM students ORDER BY city, name;

Module 3 covers sorting in depth. For now, one rule that every exercise in the course relies on: if the output is printed or compared, the query ends in an ORDER BY that fixes the order completely — ordering by the primary key is the simplest way to guarantee that.

SELECT without a table

SELECT can compute values from nothing:

SELECT 2 + 3 AS total, 'hello' AS greeting;

One row, computed from literals. It is the quickest way to try out an expression or a function. MySQL, PostgreSQL, SQLite and SQL Server accept it as written; Oracle needs a table and supplies the one-row dummy table DUAL (SELECT 2 + 3 FROM dual), which MySQL also accepts for compatibility.

Mixing columns and values

A select list can hold any mix of columns, literals and expressions:

SELECT name, 'student' AS role, year + 1 AS next_year
FROM students
ORDER BY id;

The literal 'student' is repeated on every row; year + 1 is computed per row. The stored data is untouched — SELECT only reads. Expressions get a lesson of their own in the next module.

Qualified names

A column can be written with its table in front:

SELECT students.name, students.city FROM students;

With one table it changes nothing. With two tables in a join, both of which might have a name column, the prefix is how you say which one you mean — and a short table alias (FROM students AS s, then s.name) keeps it readable. You will meet that in the joins module.

Formatting habits

Write a clause per line, keywords in capitals, a column per line once there are more than three or four:

SELECT id,
       name,
       city
FROM   students
ORDER  BY id;

The database does not care. Your colleagues, your future self and your interviewer do, and the clause-per-line layout makes the next lesson's subject — the order in which clauses run — easy to see.

Reading an error

Typical first-week errors and what they mean:

• Unknown column 'nmae' in 'field list' — a typo in a column name, or a column of another table.
• Table 'school.student' doesn't exist — the table name is wrong (students, plural).
• You have an error in your SQL syntax … near 'FROM students' — usually a trailing comma or a missing one just before the quoted text.

The quoted fragment is where the parser gave up; the mistake is usually just before it.

Key takeaways

• SELECT columns FROM table returns those columns for every row; the result is a table.
• Separate columns with commas; a missing comma silently turns the next name into an alias.
• SELECT * is for exploring; list columns in code.
• Rows have no order without ORDER BY; DESC reverses; a second column breaks ties.
• SELECT can compute from literals with no table; Oracle needs FROM dual.
• Literals and expressions can sit beside columns; SELECT never changes the data.