SQL online course

SQL SELECT Statement: Retrieve Data from a Table

Learn how to use SQL SELECT to retrieve specific columns or every column from a table, understand result sets, and avoid common query errors.

The SQL SELECT statement reads data from a database table or another data source and returns it as a result set. A basic query specifies two things: the columns to return and the table to read.

A table stores related data in columns and rows. A column is a named field, such as name or address. A row is one record in the table. A SELECT query reads these records; it does not change the table contents.

Basic SELECT Syntax

The general form for selecting particular columns is:

SELECT column_name1, column_name2
FROM table_name;

SELECT is the SQL command that requests data. The comma-separated column list identifies the fields to return. FROM introduces the data source, and table_name identifies the table being queried.

The semicolon marks the end of the statement. Many SQL tools support or expect it, although whether it is required can depend on the database system or the tool you use.

How the parts work

  1. SELECT tells the database to retrieve data.
  2. column_name1, column_name2 specifies which columns should appear in the result.
  3. FROM identifies the source of the data.
  4. table_name is the name of the table to read.

For broader SQL syntax conventions, see SQL Syntax.

Example Customer Table

The following examples use a table named Customer:

id | name       | address              | city    | state | zip
---+------------+----------------------+---------+-------+-----
1  | Bill Smith | 123 Main Street      | Hope    | CA    | 98765
2  | Mary Smith | 123 Dorian Street    | Harmony | AZ    | 98765
3  | Bob Smith  | 123 Laugh Street     | Humor   | CA    | 98765

This table has six columns: id, name, address, city, state, and zip. It contains three rows, one for each customer.

Selecting Particular Columns

Use an explicit column list when you need only certain fields:

SELECT id, name, address
FROM Customer;

This query reads the id, name, and address fields. It does not include city, state, or zip in the output.

The result set contains every row in Customer because the query has no row-filtering clause:

id | name       | address
---+------------+-------------------
1  | Bill Smith | 123 Main Street
2  | Mary Smith | 123 Dorian Street
3  | Bob Smith  | 123 Laugh Street

The returned columns appear in the same order as the SELECT list: first id, then name, then address. Selecting fewer columns produces a narrower result set than the source table, while still returning all source rows.

To return only rows that meet a condition, continue with the SQL WHERE clause.

Selecting Every Column with an Asterisk

An asterisk (*) is a wildcard that requests every available column from the specified source:

SELECT *
FROM Customer;

For the sample table, the result includes id, name, address, city, state, and zip for all three customer rows. The database expands the asterisk to the table's available columns when it processes the query.

SELECT * is convenient while exploring a table or checking its contents. In production queries, prefer explicit column names when you need only some fields:

SELECT id, name
FROM Customer;

An explicit list makes the query clearer and avoids returning unnecessary data. It also prevents a query from unexpectedly returning newly added columns if the table schema changes.

Explicit columns versus SELECT *

  • Explicit list: returns only the named columns, in the order written. It is usually clearer and more stable for application code.
  • Asterisk: returns every column available from the source. It is useful for quick inspection but may return more data than intended.

Understanding Query Results

A SELECT result is called a result set: a collection of returned columns and rows. In a simple query, each source-table row can appear as a result row, and each requested column becomes a result column.

For example, this query returns all three records from Customer, but only two fields from each record:

SELECT name, city
FROM Customer;

The result set has two columns, name and city, and three rows. The database does not return id, address, state, or zip because they are not in the SELECT list.

A basic SELECT without a filtering clause returns every row in its source table. To control row order, study the SQL ORDER BY clause. To limit how many rows are returned, see SQL SELECT LIMIT.

SQL Keywords and Identifiers

SELECT and FROM are SQL keywords. Developers commonly capitalize keywords to make queries easier to read:

SELECT id, name
FROM Customer;

SQL keyword case handling varies by database system, but capitalization is generally a formatting convention rather than a change in meaning.

Customer, id, and name are identifiers. An identifier is the name of a database object, such as a table or column. Identifiers must match the names defined in the database. Identifier case sensitivity and quoting rules vary by SQL dialect, so a name that works unquoted in one system may need different casing or quoting in another.

SELECT Does Not Modify the Table

A basic SELECT retrieves data only. It does not insert, update, or delete rows, and it does not alter the table definition. Statements such as INSERT INTO, UPDATE, and DELETE are used for data modifications.

Troubleshooting SELECT Queries

Column does not exist

Likely cause: The column name is misspelled, is not part of the table, or does not follow the database's case and quoting rules.

Resolution: Check the table schema and use the exact valid column identifier.

Table does not exist

Likely cause: The table name is incorrect, the connection uses the wrong database or schema, or the identifier requires quoting.

Resolution: Verify the table name and current database or schema, then apply the identifier rules required by the SQL dialect.

More fields appear than expected

Likely cause: The query uses SELECT *.

Resolution: Replace the asterisk with an explicit list of the columns the application needs.

All records appear unexpectedly

Likely cause: A basic SELECT without a filtering clause returns every row in the source table.

Resolution: Learn the WHERE clause to restrict rows by a condition.

Exam-Relevant Notes

  • SELECT retrieves data; FROM identifies its source.
  • A comma-separated SELECT list chooses particular columns.
  • The output column order follows the order of the SELECT list.
  • SELECT * requests every column from the specified source.
  • Without a filtering clause, a basic SELECT returns all source rows.
  • A SELECT query reads data and does not modify table contents.
  • Keyword capitalization improves readability, while identifier and quoting rules depend on the SQL dialect.

Key Takeaway

Use SELECT column1, column2 FROM table_name; to retrieve specific fields, or SELECT * FROM table_name; to retrieve every field. Prefer explicit columns when the query needs only part of a table, and remember that a basic SELECT returns all rows unless you add a filtering clause.