Beginner3 min read

Retrieving Data with the SELECT Statement

The Foundation of Data Retrieval

The SELECT statement is the most frequently used command in the Structured Query Language (SQL). Its primary purpose is to retrieve information from one or more tables within a database. A basic query requires you to specify two essential pieces of information: exactly what data you want to retrieve, and from which specific table you want to retrieve it. In SQL, you do not need to provide step-by-step programming instructions outlining how the computer should navigate the files; you simply declare the data you want, and the database engine determines the most efficient way to fulfill your request .

Retrieving All Columns

When exploring a new dataset, you may want to view all the information contained within a specific table. Instead of typing out every individual column name, you can use the asterisk (*) wildcard character.

POSTGRESQL CODE SNIPPET

SELECT *
FROM products;

🚀 Try in Playground

The asterisk acts as a shorthand instruction telling the database system to return all columns and all rows from the specified table. While this is an excellent tool for initial data exploration, it is strictly discouraged in production applications. Retrieving entire tables can heavily strain network bandwidth and slow down application performance, especially when the end-user only needs a very small subset of the available data.

Retrieving Specific Columns

To write more efficient and maintainable queries, you should explicitly list the exact columns you want to retrieve. By placing the desired column names immediately after the SELECT keyword and separating them with commas, you restrict the output to only the necessary data points.

POSTGRESQL CODE SNIPPET

SELECT product_name, price, quantity
FROM products;

🚀 Try in Playground

This targeted approach significantly improves query performance. Additionally, the order in which you list the columns in your SELECT statement dictates their exact order in the final result set. This allows you to present the data in a logical sequence for reporting, regardless of how the columns are physically structured in the underlying database table.

Providing Column Aliases

Database column names are often abbreviated or formatted in ways that are difficult for business users to read. You can use the AS keyword to assign a temporary nickname, or alias, to any column in your result set.

POSTGRESQL CODE SNIPPET

SELECT product_name AS name, quantity AS current_stock
FROM products;

🚀 Try in Playground

An alias only exists for the duration of the query. It does not alter the physical table structure or permanently change the original column names. Aliases are a highly practical way to make your reports cleaner and your SQL code completely self-documenting.

Retrieving Unique Values

A table may contain hundreds of duplicate values within a single column. If you only want to see the unique values present in a dataset, you can apply the DISTINCT keyword.

POSTGRESQL CODE SNIPPET

SELECT DISTINCT category
FROM products;

🚀 Try in Playground

When placed immediately after the SELECT keyword, DISTINCT instructs the database to evaluate the specified column and return only unique occurrences, effectively filtering out all redundant duplicates from your view.

Limiting the Result Set

When querying tables with millions of records, returning every row is rarely necessary and can overwhelm your system resources. You can restrict the total number of rows returned by your query using the LIMIT clause.

POSTGRESQL CODE SNIPPET

SELECT product_name, price
FROM products
LIMIT 5;

🚀 Try in Playground

This command stops the database from processing further once it has retrieved the specified number of rows, keeping your queries fast. Note that while LIMIT is standard in PostgreSQL, MySQL, and SQLite, other systems might use TOP or FETCH FIRST to achieve the exact same operational result.

← Previous ChapterIntroduction to Relational Databases and SQL Next Chapter →Sorting Retrieved Data with ORDER BY