SQLQuerying Data with SELECTbeginner40 min read

Retrieving Data with SELECT

Learn SQL query fundamentals: retrieve data using SELECT, choose columns, evaluate expressions, use aliases, and understand the logical processing of a query.

In this lesson, you will learn

✓Write SELECT ... FROM statements to retrieve table data
✓Select specific columns instead of retrieving every column
✓Evaluate arithmetic, string, and function expressions in SELECT
✓Use column aliases to make query results easier to understand
✓Explain the conceptual logical processing order of a SQL query
✓Understand why SELECT * is often unsuitable for production application code
✓Distinguish a query's written order from its logical processing order
✓Recognize that result ordering is not guaranteed without ORDER BY

What Does SELECT Do?

SELECT is the SQL statement used to retrieve data. It returns a result set containing the expressions you request. A result set is often displayed as a table of rows and columns. SELECT does not normally change the stored rows; statements such as INSERT, UPDATE, and DELETE are used for data changes. The simplest SELECT query chooses columns from a table.

Retrieve Employee Data

sql

1
2
SELECT *
FROM employees;

Output

The asterisk requests all columns from employees. These sample rows illustrate the output; actual rows and their display order depend on the database contents and query.

Understanding SELECT and FROM

SELECT tells the database what values or expressions should appear in the result. FROM tells it where to obtain the data. The following query returns only the employee names, even if the source table contains many additional columns.

Choose Specific Columns

sql

1
2
SELECT first_name, last_name
FROM employees;

Output

Only first_name and last_name are returned. The other columns remain in the source table but are not included in this result.

Understanding SELECT *

The * symbol is a shorthand for all columns exposed by the FROM source. It can be convenient while exploring an unfamiliar table or working through a quick experiment. In application code, explicitly listing the required columns is often safer because it makes the query's output contract clear and avoids transferring data the application does not need.

A More Focused Application Query

sql

1
2
3
4
5
6
7
8
9
-- Broad query: may return more data than needed
SELECT *
FROM user_profiles
WHERE user_id = 42;
 
-- Focused query: request only required fields
SELECT user_id, display_name, avatar_url
FROM user_profiles
WHERE user_id = 42;

Output

The second query returns only user_id, display_name, and avatar_url for the matching row.

Explicit columns reduce unnecessary output and make the query's intended result easier to review. Do not return password hashes or other sensitive fields merely because they exist in the table.

A Practical Rule

Use SELECT * when exploring data if it is appropriate. In production APIs and application queries, explicitly select the fields the caller needs, and enforce authorization separately.

SELECT * Compared with Explicit Columns

Aspect	SELECT *	Explicit columns
Writing speed	Short and convenient	Requires listing column names
Output shape	Can change when source columns change	More clearly defined by the query
Data transfer	May include unnecessary values	Can limit returned data to what is needed
Sensitive fields	May return fields the caller should not receive	Makes it easier to restrict returned fields
Exploration	Useful for quick inspection	Useful for targeted analysis and application code

Selecting Expressions

A SELECT list can contain more than stored column names. It can also contain arithmetic expressions, string expressions, constants, and function calls. The database evaluates these expressions and returns their results as columns in the result set.

Perform Arithmetic in SQL

sql

1
2
3
4
5
SELECT
  10 + 5 AS addition,
  10 - 5 AS subtraction,
  10 * 5 AS multiplication,
  10.0 / 4 AS division;

Output

addition | subtraction | multiplication | division ---------+-------------+----------------+--------- 15 | 5 | 50 | 2.5000

SQL can calculate values directly. Aliases give the calculated columns readable names. Exact formatting and decimal scale can vary by client and expression type.

Using SELECT Without FROM

PostgreSQL permits a SELECT statement that evaluates expressions without reading a table. This is useful for checking functions, testing calculations, and displaying current date or time values. Some other database systems have different conventions or syntax requirements.

Evaluate Expressions and Functions

sql

1
2
3
4
5
SELECT
  25 * 4 AS multiplication,
  ROUND(145.789::numeric, 2) AS rounded_value,
  UPPER('codenivo') AS uppercase_text,
  CURRENT_DATE AS today;

Output

One row containing multiplication = 100, rounded_value = 145.79, uppercase_text = CODENIVO, and today's date according to the PostgreSQL session environment.

No FROM clause is needed for these expressions in PostgreSQL. The date is intentionally environment-dependent rather than fixed to a particular day.

Understanding Column Aliases

An alias is a temporary name assigned to an output expression. Use AS to make the result easier to read. The alias affects how a column is labeled in the result set; it does not rename the stored column in the table.

Rename Result Columns

sql

1
2
3
4
5
SELECT
  first_name AS given_name,
  last_name AS family_name,
  salary * 12 AS annual_salary
FROM employees;

Output

A result with the column labels given_name, family_name, and annual_salary.

The aliases make the returned values clearer. The expression salary * 12 calculates a value for the query result; it does not create a permanent annual_salary column.

Using String Functions

String functions transform text values in a query result. For example, UPPER changes letters to uppercase, LOWER changes letters to lowercase, and LENGTH returns the length of a string in characters in PostgreSQL. These expressions do not modify the stored values.

Transform Text Values

sql

1
2
3
4
5
SELECT
  UPPER(first_name) AS uppercase_name,
  LOWER(department) AS lowercase_department,
  LENGTH(first_name) AS name_length
FROM employees;

Output

One row per employee with uppercase_name, lowercase_department, and name_length columns.

The functions transform or measure values for the result. The original first_name and department values are unchanged.

Using Numeric Functions

Numeric functions allow a query to round, measure, or otherwise calculate numeric values. ROUND is especially useful for presentation and calculations that require a defined decimal scale. Keep in mind that rounding a displayed result is not the same as changing how a value is stored.

Calculate and Round Salary Values

sql

1
2
3
4
5
SELECT
  first_name,
  salary,
  ROUND(salary * 1.10, 2) AS salary_after_raise
FROM employees;

Output

A result containing each employee's original salary and a calculated salary_after_raise equal to 110% of salary, rounded to two decimal places.

This query only calculates a projected value. It does not update employees.salary.

Combining Expressions and Columns

A SELECT list can mix stored columns with calculations and function calls. This is useful when a report needs both the original values and derived information. Use clear aliases so the meaning of each output column is obvious.

A Simple Employee Report

sql

1
2
3
4
5
6
7
SELECT
  emp_id,
  first_name || ' ' || last_name AS full_name,
  department,
  salary,
  ROUND(salary / 12, 2) AS monthly_salary
FROM employees;

Output

A report containing employee ID, full name, department, original salary, and the calculated salary divided by 12.

In PostgreSQL, || concatenates strings. The monthly_salary expression is a calculation; its meaning assumes salary represents an annual amount.

The Logical Processing Order of a Query

SQL has a written order and a conceptual logical processing order. Understanding the logical model explains why some expressions and aliases are available in certain clauses but not others. The database optimizer may reorder or transform operations in its physical execution plan while preserving the query's required result.

Logical Order Is a Learning Model

This sequence describes the logical relationships between query clauses. It is not a guarantee that the database physically executes each operation in this exact order.

Simplified Logical Processing Order

Logical stage	Clause	Purpose
1	FROM	Identify source tables and determine the rows produced by joins
2	WHERE	Filter individual rows before grouping
3	GROUP BY	Form groups for aggregate calculations
4	HAVING	Filter groups after grouping
5	SELECT	Compute the requested output expressions
6	DISTINCT	Remove duplicate output rows when requested
7	ORDER BY	Sort the result when an ordering is requested
8	LIMIT / OFFSET	Restrict which rows are returned

Why a SELECT Alias Cannot Usually Be Used in WHERE

At the same query level, WHERE is logically evaluated before the SELECT list produces its output aliases. Therefore, a SELECT alias is generally unavailable to WHERE. You can repeat the expression in WHERE or use a subquery or CTE to make the calculated value available as an input column to an outer query.

Filter Using a Calculated Value

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Incorrect in PostgreSQL:
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;
 
-- Option 1: repeat the expression
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;
 
-- Option 2: use a subquery
SELECT annual_salary
FROM (
  SELECT salary * 12 AS annual_salary
  FROM employees
) AS employee_salaries
WHERE annual_salary > 100000;

Output

The corrected queries return calculated annual salary values greater than 100000, assuming salary is an annual amount in the first query's intended business meaning.

The subquery gives the outer query a source column named annual_salary. That outer query can then filter it with WHERE.

Why ORDER BY Matters

A SELECT query without ORDER BY does not guarantee the order of its rows. Results may appear in a familiar order during testing but change when the query plan, data, indexes, or database version changes. If order matters to a report, user interface, or test, specify it explicitly.

Request a Predictable Sort Order

sql

1
2
3
SELECT emp_id, first_name, salary
FROM employees
ORDER BY salary DESC, emp_id ASC;

Output

Employees ordered by salary from highest to lowest. If salaries tie, emp_id sorts those rows from lowest to highest.

The second sort key provides a tie-breaker. A unique final sort key is especially helpful when stable ordering is important for pagination.

SELECT with DISTINCT

DISTINCT removes duplicate rows from the selected result. When selecting one column, it returns each distinct value once. When selecting multiple columns, it removes duplicate combinations of all selected values, not duplicates in each column independently.

List Distinct Departments

sql

1
2
3
SELECT DISTINCT department
FROM employees
ORDER BY department;

Output

One row per distinct department, sorted alphabetically. For the sample employees, the departments are Engineering, Finance, and Marketing.

DISTINCT removes repeated department values from the result. ORDER BY determines the displayed order.

Common SELECT Errors

Many beginner errors come from confusing result aliases with stored column names, omitting the source table, or assuming the database returns rows in a fixed order. A careful check of the SELECT list, FROM clause, and result requirements can prevent these mistakes.

Troubleshooting SELECT Queries

Problem	Likely cause	Correction
Column does not exist	A misspelled column name or unavailable alias	Check the source schema and the clause where the alias is used
Relation does not exist	A table name or schema is incorrect	Check the database, schema, and table name
Unexpected row order	The query does not specify ORDER BY	Add the required sort columns
Unexpected duplicate rows	The source contains repeated values or joins multiply rows	Inspect the relationship and use DISTINCT only if duplicate removal is intended
Too many columns returned	The query uses SELECT *	Select only the required columns

MCQ 1: SELECT Basics

Check your understanding of the purpose of SELECT.

MCQ 2: Choosing Columns

Check how a SELECT list affects the result.

MCQ 3: Aliases

Understand what an alias changes.

MCQ 4: SELECT Without FROM

Check your understanding of PostgreSQL expression queries.

MCQ 5: Logical Processing

Review the simplified logical processing model.

MCQ 6: Aliases and WHERE

Check why a result alias cannot usually be used in WHERE at the same query level.

MCQ 7: Row Ordering

Check when query output ordering is guaranteed.

MCQ 8: DISTINCT

Understand how DISTINCT behaves.

Guided Practice: Build a Simple Report

Assume the employees table has emp_id, first_name, last_name, department, and salary columns. Write a query that returns employee ID, full name, department, and monthly salary. Label the calculated column monthly_salary and sort by salary from highest to lowest. Do not modify the stored table data.

Your Practice Requirements

sql

1
2
3
4
5
6
7
-- Write a SELECT query that:
-- 1. Returns emp_id.
-- 2. Combines first_name and last_name into full_name.
-- 3. Returns department.
-- 4. Calculates salary / 12 as monthly_salary.
-- 5. Sorts by salary descending.
-- 6. Uses emp_id as a tie-breaker.

Output

A result set containing emp_id, full_name, department, and monthly_salary, sorted by descending salary with emp_id as the tie-breaker.

Use the concatenation operator || in PostgreSQL. Remember that ORDER BY can use a source column even if that column is not included in the result list.

Guided Practice Solution

Compare your query with this solution. The result includes only the requested fields, and the ordering is explicit.

Employee Report Solution

sql

1
2
3
4
5
6
7
SELECT
  emp_id,
  first_name || ' ' || last_name AS full_name,
  department,
  ROUND(salary / 12, 2) AS monthly_salary
FROM employees
ORDER BY salary DESC, emp_id ASC;

Output

A sorted report with employee ID, full name, department, and monthly salary.

The query reads data without changing it. The monthly salary calculation assumes salary stores an annual amount.

Interview Questions and Answers

Use these questions to test your understanding of foundational SELECT queries.

Lesson Summary

SELECT retrieves columns and calculated expressions from a data source. FROM identifies the source, aliases label output columns, and functions transform or calculate values for the result. SELECT * is convenient for exploration, but explicit columns are usually better for production application queries. The logical processing model explains clause behavior, including why SELECT aliases are generally unavailable in WHERE. Add ORDER BY whenever row order matters, and use DISTINCT only when duplicate removal is part of the intended result.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using SELECT * in production backend code

Why it happens: SELECT * is quick to type during exploration, so it can be copied into application code without considering the returned fields.

Incorrect Approach

sql

SELECT * FROM user_profiles WHERE user_id = 42;

Correct Solution

sql

SELECT user_id, display_name, avatar_url
FROM user_profiles
WHERE user_id = 42;

Key takeaway: Explicit columns reduce unnecessary output, make the expected result clearer, and help avoid unintentionally returning sensitive or large fields. SELECT * does not inherently prevent index-only scans; performance depends on the query, indexes, and execution plan.

#2 Assuming SELECT * returns every row in the database

Why it happens: The asterisk can be mistaken for a wildcard over all database data.

Incorrect Approach

sql

SELECT *;

Correct Solution

sql

SELECT * FROM employees;

Key takeaway: The asterisk requests all columns from the query's source. FROM identifies that source, and the query returns its matching rows.

#3 Using a SELECT alias in WHERE at the same query level

Why it happens: The alias appears in the SQL text, so it may seem available to every clause.

Incorrect Approach

sql

SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;

Correct Solution

sql

SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;

Key takeaway: WHERE is logically processed before the SELECT list produces the alias. A subquery or CTE is another valid way to filter by the calculated output.

#4 Expecting a fixed row order without ORDER BY

Why it happens: Rows may appear in the same order during several test runs.

Incorrect Approach

sql

SELECT emp_id, first_name FROM employees;

Correct Solution

sql

SELECT emp_id, first_name
FROM employees
ORDER BY emp_id;

Key takeaway: The database is not required to return rows in a predictable order unless you request one.

#5 Confusing an alias with a permanent table column

Why it happens: A calculated result appears with a meaningful name, making it look like a stored field.

Incorrect Approach

sql

SELECT salary * 12 AS annual_salary FROM employees;
-- This does not add annual_salary to employees.

Correct Solution

sql

SELECT salary * 12 AS annual_salary
FROM employees;

Key takeaway: The alias exists in the query result. To add a persistent column, you would need a schema change and a deliberate data-maintenance design.

#6 Using DISTINCT to hide unexpected duplicates without investigation

Why it happens: DISTINCT makes repeated output rows disappear, which can hide a faulty join or misunderstood relationship.

Incorrect Approach

sql

SELECT DISTINCT c.customer_id, o.order_id
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id;

Correct Solution

sql

SELECT c.customer_id, o.order_id
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id;

Key takeaway: Inspect the join and expected relationship first. DISTINCT is useful when duplicate elimination is genuinely required, not as an automatic fix for every repeated result.

#7 Assuming a SELECT calculation changes stored data

Why it happens: The query returns a transformed or rounded value that looks like the original value has changed.

Incorrect Approach

sql

SELECT ROUND(salary, 0) AS rounded_salary FROM employees;

Correct Solution

sql

SELECT salary, ROUND(salary, 0) AS rounded_salary
FROM employees;

Key takeaway: Both expressions only return values in the result set. Stored data remains unchanged unless a data-modification statement is executed.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Retrieving Data with SELECT Knowledge Check

Test your understanding with immediate feedback

Question 1 of 8

What is SELECT primarily used for?

Question 2 of 8

Which query returns only first_name and salary from employees?

Question 3 of 8

What does salary * 12 AS annual_salary do?

Question 4 of 8

Which statement is valid in PostgreSQL?

Question 5 of 8

Which clause logically filters individual rows before GROUP BY?

Question 6 of 8

Why does WHERE generally not recognize a SELECT alias?

Question 7 of 8

Which clause should you use when result rows must be sorted?

Question 8 of 8

What does SELECT DISTINCT department FROM employees return?

Technical and placement interview questions

Frequently tested interview questions with detailed, full answers (click to expand)

Q1:What is SELECT in SQL?

Answer:

SELECT retrieves data or evaluates expressions and returns a result set.

Q2:What does FROM do?

Answer:

FROM identifies the tables or other data sources used by the query.

Q3:What does SELECT * mean?

Answer:

It requests all columns exposed by the query's FROM source. It does not necessarily retrieve every row in a database.

Q4:Why is SELECT * often avoided in production code?

Answer:

It may return unnecessary data, expose fields the caller does not need, and make an application's expected output less explicit.

Q5:What is a column alias?

Answer:

An alias is a name assigned to a result expression, usually with AS. It changes the result label, not the stored table schema.

Q6:Can PostgreSQL run SELECT without FROM?

Answer:

Yes. PostgreSQL can evaluate constants, calculations, and functions in a SELECT statement without a FROM clause.

Q7:What is the difference between written and logical query order?

Answer:

Written order is how the SQL statement is arranged. Logical processing order is a conceptual model for how clauses depend on one another.

Q8:Why can a SELECT alias not usually be used in WHERE?

Answer:

WHERE is logically evaluated before the SELECT list produces its aliases. Repeat the expression or use a subquery or CTE.

Q9:Does SELECT guarantee row order?

Answer:

No. Specify ORDER BY when a particular order is required.

Q10:What does DISTINCT do?

Answer:

DISTINCT removes duplicate rows from the selected output. With multiple columns, uniqueness is evaluated across the selected combination.

Q11:Can SELECT calculate values?

Answer:

Yes. It can evaluate arithmetic expressions, string operations, and functions without updating the stored data.

Q12:Does ROUND in SELECT update the original value?

Answer:

No. ROUND returns a calculated result for that query. An UPDATE statement would be needed to change stored values.

Q13:What is the purpose of ORDER BY?

Answer:

ORDER BY requests a particular result ordering based on one or more expressions.

Q14:Can ORDER BY use a column not included in SELECT?

Answer:

In a simple non-distinct query, yes, a source column can be used for sorting even when it is not returned. There are restrictions in some query forms, including certain DISTINCT queries.

Q15:Does the logical query order describe the exact physical execution plan?

Answer:

No. The optimizer may choose a different physical plan as long as the query's required semantics are preserved.

Key takeaways

SELECT specifies the expressions and columns returned by a query.
FROM identifies the source table or other data source.
SELECT * requests all columns from the selected source, not necessarily all rows from the database.
Selecting only the columns an application needs can reduce unnecessary data transfer and coupling to the schema.
PostgreSQL supports expression-only SELECT statements without a FROM clause.
AS assigns an alias to a result column or table reference.
The logical processing model helps explain SQL behavior, but the database optimizer can choose a different physical execution plan.
Without ORDER BY, row order is not guaranteed.
Aliases defined in the SELECT list generally cannot be referenced by the WHERE clause at the same query level.

Was this lesson helpful?

Found an error? Report an issue