SQL SELECT

SQL SELECT

How to Get Exactly the Data You Need

Gaurav Shegekar

Oct 19

Imagine you’re building an e-commerce application.

A customer opens the order history page. Your backend needs to fetch that customer’s orders, display the product names, and show the latest orders first. Your database contains thousands of orders, but the page needs only a few records. How do you retrieve the right information without bringing unnecessary data into your application?

This is where the SQL SELECT statement comes in. It is one of the first SQL commands developers learn, but using it properly is more important than simply knowing its syntax.

What Is the SELECT Statement?

The SELECT statement retrieves data from one or more database tables. You can choose which columns to return, filter rows using conditions, sort the results, and limit how many records you receive. Almost every backend application uses SELECT queries, whether it is fetching a user’s profile, checking product availability, or displaying transaction history.

Let’s use a simple products table throughout this article.

product_idproduct_namepricecategory101Keyboard1500Accessories102Mouse700Accessories103Monitor12000Electronics104Headphones2500Accessories105Laptop65000Electronics

Suppose your application needs to display these products on a webpage. You don’t need to write Java code to retrieve every row manually. You can ask the database for the required information using a single SQL query.

1. Retrieve All Columns Using SELECT *

The simplest way to retrieve every column from a table is to use the asterisk (*) symbol.

SELECT *
FROM products;

This query returns all columns and all rows from the products table. It is useful when you’re exploring a database, debugging an issue, or checking sample data during development. However, there is something important to remember: retrieving everything is not always the best choice.

Imagine your users table contains personal details, addresses, profile information, and other fields that a particular API doesn’t need. Fetching every column just to display a username and email adds unnecessary data to the query result. In real applications, request only what you actually need.

2. Select Only the Columns You Need

Instead of retrieving every column, you can specify the exact columns required by your application.

SELECT product_name, price
FROM products;

The result contains only the product names and prices. The database doesn’t include product_id or category in the returned result because you didn’t request them. This makes the query easier to understand and keeps the result focused on the task.

This becomes especially useful when building REST APIs with Spring Boot. If an endpoint displays product names and prices, selecting those columns can reduce unnecessary data retrieval and transfer. Whether it improves performance significantly depends on the table, indexes, query plan, and workload, but selecting only required columns is a sensible starting point.

3. Use WHERE to Filter Records

Now imagine your application has 50,000 products, but a customer wants to see only products priced below ₹2,000. Returning every product and filtering them inside Java would be an unnecessary way to handle the problem when SQL can apply the condition directly.

SELECT product_name, price
FROM products
WHERE price < 2000;

This query returns the Keyboard and Mouse because their prices are below ₹2,000. The WHERE clause determines which rows qualify for the result. You can also combine conditions using operators such as AND and OR.

For example, suppose you want accessories that cost less than ₹2,000.

SELECT product_name, price
FROM products
WHERE category = 'Accessories'
  AND price < 2000;

The database returns only products satisfying both conditions. This is a useful habit for backend developers: let the database filter the records instead of fetching a large result set and filtering it in application code without a good reason.

4. Use ORDER BY to Sort Results

A query may return the correct products, but your application might need to display them from the cheapest to the most expensive. The ORDER BY clause handles this.

SELECT product_name, price
FROM products
ORDER BY price ASC;

ASC means ascending order, while DESC means descending order. If you want the most expensive products first, change the last line to ORDER BY price DESC. Ascending order is the default when you don’t specify a direction.

One important detail: a database does not guarantee the order of results unless you specify ORDER BY. If your application displays the latest orders first, explicitly sort by the relevant timestamp. For stable pagination, include a unique tie-breaker, such as an order ID, when timestamps can be identical.

5. Use LIMIT to Retrieve a Few Rows

Suppose your homepage displays only the three most expensive products. You don’t need to retrieve every product and remove the extras in Java.

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 3;

The query returns the three highest-priced products. ORDER BY determines which products come first, and LIMIT restricts the number of rows returned. This syntax works in MySQL and PostgreSQL, although some other database systems use different syntax for limiting results.

You will often see this pattern in product listings, dashboards, and small result previews. For larger datasets, pagination requires additional thought, especially when using OFFSET on deep pages. Still, understanding ORDER BY and LIMIT gives you the foundation for building useful database queries.

6. Use DISTINCT to Remove Duplicate Results

Imagine you want to display all product categories available in your store. Selecting the category column directly could return the same category several times because multiple products belong to it.

SELECT DISTINCT category
FROM products;

DISTINCT removes duplicate values from the returned result, so each category appears once. This is useful when generating category filters, collecting unique status values, or exploring the data in a table.

Remember that DISTINCT applies to the combination of selected columns. If you select both category and price, two rows with the same category but different prices are not duplicates. Use it when you need unique results, rather than adding it automatically to every query.

7. Give Columns Readable Names with Aliases

SQL also lets you give a column or expression a temporary name in the query result. This is called an alias, and it can make results easier to read.

SELECT
    product_name AS name,
    price AS product_price
FROM products;

The result displays the column labels name and product_price instead of their original names. Aliases are particularly useful when queries contain calculations, joins, or expressions that would otherwise produce awkward column labels.

For example, SELECT price * 2 AS double_price FROM products; returns a calculated value under the name double_price. The alias changes the output label, not the underlying column stored in the table.

A Practical SELECT Query

Let’s combine several concepts into one query. Imagine you’re building a product page that should display the three cheapest accessories first, but only those costing more than ₹500.

SELECT product_name AS name, price
FROM products
WHERE category = 'Accessories'
  AND price > 500
ORDER BY price ASC
LIMIT 3;

Read the query as a sequence of decisions: retrieve the product name and price, look in the products table, keep only matching accessories, sort them by price, and return at most three rows. This is a common pattern in real backend development, where an API needs a specific set of records rather than an entire table.

One last detail: the database’s logical query processing order is not the same as the order in which you write the SQL clauses. For a typical query like this, FROM and WHERE are logically processed before SELECT, followed by ORDER BY and LIMIT. The database engine may choose a different physical execution plan to run the query efficiently.

The Takeaway

The SELECT statement is more than a command for displaying database records. It lets you control which columns you retrieve, which rows qualify, how results are sorted, and how many records are returned. Learning these basics helps you write clearer SQL and build more predictable backend APIs.

Here’s a question for Java developers: when an API needs only two columns from a table containing twenty columns, would you use SELECT * or explicitly select those two columns? Why?

Share your reasoning in the comments. The small decisions we make in everyday queries can have a real impact on how we build and maintain applications.

Follow ByteCraft for practical Java, SQL, and backend development lessons without the unnecessary complexity.

ByteCraft is free today. But if you enjoyed this post, you can tell ByteCraft that their writing is valuable by pledging a future subscription. You won't be charged unless they enable payments.

Pledge your support

© 2026 Gaurav Shegekar
548 Market Street PMB 72296, San Francisco, CA 94104