Query Data from a MySQL Table with SELECT

Learn how to use MySQL SELECT to retrieve named columns or every column from a table, read result sets, and troubleshoot common query errors.

What a database query does

A query is a request for data or information from a database. Querying lets you read data that is stored in one or more tables without changing the stored records.

A table is a database object that stores data in rows and columns. A row is one record, such as one person's details. A column is a named attribute or field, such as a person's first name, surname, or birth year.

A query can return a subset of a table's columns, a subset of its rows, or both. This lesson focuses on choosing columns with SELECT. An unfiltered query returns every row; row filtering is normally added with a WHERE clause.

The SELECT statement

SELECT is the SQL keyword used to retrieve data. The part after SELECT is the column list: the comma-separated set of columns you want returned. The FROM clause identifies the source table.

SELECT column_name, column_name FROM table_name;

Read this statement from left to right:

SELECT says that the statement will read data.
column_name, column_name specifies which columns to return.
FROM identifies the table that contains those columns.
table_name is the name of that table.
; ends the SQL statement in the MySQL command-line client.

Replace the example names with the real column and table names in your database.

Run SELECT in the MySQL client

Queries are entered at the mysql> prompt. The following examples use a table named testtb with three columns: name, surname, and year.

mysql> SELECT name, surname FROM testtb;
+------+-----------+
| name | surname   |
+------+-----------+
| Amy  | Goodridge |
| Mark | Smith     |
+------+-----------+
2 rows in set (0.00 sec)

The semicolon tells the client that the statement is complete. After executing the query, MySQL displays the result set, which is the rows and columns returned by the query.

Select named columns

To retrieve particular fields, write their names after SELECT. Separate multiple column names with commas.

SELECT name, surname FROM testtb;

This query returns only the name and surname columns. It does not return year, even though year is also defined in testtb.

The output columns appear in the same order as the names in the SELECT list. For example:

SELECT surname, name FROM testtb;

This produces the surname column first and the name column second. Changing the order in the query changes the order of the result headings and values; it does not change the table itself.

Selecting named columns is useful when you need only specific information. It makes the result easier to read and avoids retrieving fields that are not needed.

One column

A query may request just one column:

SELECT name FROM testtb;

The result has one heading, name, and one value in that column for each row returned.

Several columns

For two or more columns, separate each name with a comma:

SELECT name, surname, year FROM testtb;

Do not separate column names with the word AND. In a column list, commas are the separators.

Select all columns with the asterisk

The asterisk (*) is a wildcard in a SELECT list. It means “every column available in the table named in the FROM clause.”

SELECT * FROM testtb;

If testtb contains name, surname, and year, this query returns all three columns for every row. If more columns are added to the table later, SELECT * also includes those columns.

Use SELECT * when you are inspecting a complete row or learning the contents of a table. For application queries and reports, explicitly naming the needed columns is often clearer and more stable.

Named columns versus all columns

SELECT name, surname FROM testtb;
SELECT * FROM testtb;

SELECT name, surname FROM testtb; returns two columns: name and surname.
SELECT * FROM testtb; returns every column defined in testtb, including year.
Because neither query has a WHERE clause, both return all rows in the table.

The column selection and row selection are separate decisions. The column list controls which fields appear. A WHERE clause controls which records qualify.

Read the result set

When no row filter is used, each returned result row corresponds to a row in the source table. The result headings identify the selected columns, and the values beneath each heading come from the corresponding table fields.

For the sample data, the table contains these records:

name  | surname   | year
Amy   | Goodridge | 1991
Mark  | Smith     | 1955

A query that selects two columns has a two-column result:

mysql> SELECT name, surname FROM testtb;
+------+-----------+
| name | surname   |
+------+-----------+
| Amy  | Goodridge |
| Mark | Smith     |
+------+-----------+
2 rows in set (0.00 sec)

The MySQL command-line client usually prints a row-count message such as 2 rows in set. This tells you how many result rows were returned. The timing in parentheses indicates how long the client reports the operation took. The count refers to rows in the result, not the number of columns.

Basic query examples

Retrieve a user's first name and surname

mysql> SELECT name, surname FROM testtb;

This returns a two-column result set containing the name and surname values for every row in testtb. It demonstrates targeted retrieval: the query requests only the information needed.

Retrieve every column

mysql> SELECT * FROM testtb;

This returns name, surname, year, and any other columns currently defined in testtb for every row.

Common errors and troubleshooting

Unknown column

Symptom: MySQL reports an unknown column error.

Likely cause: A selected column name does not exactly match a column defined in the table. Spelling mistakes and incorrect assumptions about the schema are common causes.

Resolution: Check the table structure, then correct the column name in the query. Make sure the intended database is selected and that the column exists in the named table.

Table does not exist

Symptom: MySQL reports that testtb, or another named table, does not exist.

Likely cause: The table name is incorrect, or the database containing the table has not been selected.

Resolution: Verify the table name and switch to the database that contains it before running the query. Review Create a Database and Create a Table for the surrounding setup.

The prompt continues instead of showing results

Symptom: The command prompt does not execute the query and continues on another input line.

Likely cause: The SQL statement has not been terminated.

Resolution: Finish the statement with a semicolon, for example:

mysql> SELECT name FROM testtb;

More data than expected

Symptom: The query returns more information than you wanted.

Likely cause: You used SELECT *, or you omitted a row filter.

Resolution: Name only the required columns and add a WHERE clause when only certain rows should be returned. For example, the following still selects two columns but limits the rows to one year:

SELECT name, surname FROM testtb
WHERE year = 1991;

Row filtering with WHERE is the next step after basic retrieval.

Summary

A query requests information stored in one or more database tables.
A table contains rows, or records, and columns, or named fields.
SELECT reads data, and FROM identifies the source table.
List column names after SELECT, separating multiple names with commas.
Result columns appear in the order written in the SELECT list.
SELECT * returns every column in the named table.
Without WHERE, a SELECT query returns all rows from the table.
End statements with a semicolon in the MySQL client.

Next steps

Basic SELECT statements can be extended to filter rows, sort results, limit the number of returned rows, rename output columns, calculate values, and combine data from multiple tables.