SQLQuerying Data with SELECTbeginner40 min read

Selecting Specific Columns

Master column projection in SQL: select exact fields, create readable aliases with AS, calculate values directly in SELECT, and concatenate strings safely.

On This Page (Table of Contents)

In this lesson, you will learn

✓Explain column projection and select specific columns from a table
✓Control the names and order of columns in a query result
✓Rename output columns using aliases
✓Perform arithmetic calculations directly in SELECT
✓Calculate totals, discounts, profit, and percentage margins
✓Concatenate text with || and CONCAT()
✓Handle nullable text values with COALESCE
✓Explain why selecting fewer columns may improve query efficiency
✓Distinguish calculated result columns from stored table columns

What Is Column Projection?

A database table can contain many columns, but a query usually needs only some of them. Column projection means choosing the columns or expressions that should appear in the result. In SQL, you control projection with the SELECT list. This differs from row filtering, which is performed with WHERE and will be covered separately.

Project Specific Customer Columns

sql

1
2
SELECT customer_id, first_name, last_name, email
FROM customers;

Output

The query returns only the four named columns, in the order written. The displayed rows are illustrative sample data.

Choosing Columns and Their Order

The order of expressions in SELECT determines the order of columns in the result. You can change that order without changing the order or structure of columns in the underlying table. You can also select the same source column more than once when the query requires it, although that is rarely necessary for simple reports.

Return Columns in a Different Order

sql

1
2
3
4
5
6
SELECT
  email,
  last_name,
  first_name,
  customer_id
FROM customers;

Output

The result columns appear in this order: email, last_name, first_name, customer_id.

The SELECT list controls the output shape. It does not rearrange the actual table schema.

Projection Versus Row Filtering

Projection controls which columns appear in the output. Filtering controls which rows qualify for the result. A query can use projection without filtering, filtering without reducing the selected columns, or both together.

Select Columns and Filter Rows

sql

1
2
3
SELECT customer_id, email
FROM customers
WHERE customer_id = 2;

Output

The customer_id and email columns for the customer whose ID is 2, if that row exists.

SELECT limits the output columns, while WHERE limits the qualifying rows.

Two Different Query Operations

Operation	SQL clause	Question answered
Projection	SELECT	Which columns or expressions should be returned?
Row filtering	WHERE	Which rows should be returned?

Why Explicit Columns Are Useful

Selecting only the columns an application needs makes the query's output contract clearer. It can reduce the amount of data transferred to the client and avoid fetching large or sensitive values unnecessarily. Depending on the available indexes, visibility information, and query plan, a narrower SELECT list may also allow an index-only scan. These benefits are workload-dependent rather than guaranteed.

Avoid Fetching Unnecessary Fields

sql

1
2
3
4
5
6
7
8
9
-- Broad result
SELECT *
FROM user_profiles
WHERE user_id = 42;
 
-- Focused result
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 profile.

The focused query has a more explicit result shape. SELECT * does not inherently prevent index-only scans, but selecting unnecessary columns can make a narrow index insufficient for the query.

A Good Application Habit

Prefer explicit columns in production queries. Return only fields required by the current operation, and enforce authorization independently of the SELECT list.

Creating Column Aliases with AS

An alias assigns a name to a column or expression in the query result. The AS keyword makes the relationship between an expression and its output name clear. Aliases are especially helpful for calculations, reporting, and application responses. They do not permanently rename columns in the underlying table.

Rename Columns

sql

1
2
3
4
5
SELECT
  first_name AS given_name,
  last_name AS family_name,
  email AS contact_email
FROM customers;

Output

The result columns are labeled given_name, family_name, and contact_email.

The aliases affect the result's column labels only. The stored columns remain first_name, last_name, and email.

Choosing Good Alias Names

Good aliases describe the meaning of a result. Use readable names such as full_name, gross_profit, or margin_percentage. Unquoted identifiers in PostgreSQL are folded to lowercase, so snake_case aliases are convenient for SQL and application code. Avoid unnecessary spaces and reserved keywords.

Use Readable Aliases

sql

1
2
3
4
5
SELECT
  product_name AS item_title,
  unit_price AS selling_price,
  unit_price - unit_cost AS gross_profit
FROM inventory_items;

Output

A result with the labels item_title, selling_price, and gross_profit.

Readable aliases make it easier for developers and report users to understand each value.

Performing Arithmetic in SELECT

SQL supports arithmetic expressions such as addition, subtraction, multiplication, and division. These calculations are useful for computing line totals, discounts, taxes, profit, and other derived values. The result is calculated when the query runs; it does not update the source row.

Basic Arithmetic

sql

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

Output

addition | subtraction | multiplication | division ---------+-------------+----------------+--------- 25 | 15 | 100 | 5.0000

The expressions are evaluated by PostgreSQL. Numeric display formatting can vary by SQL client.

Calculating Discounts and Final Prices

A discount can be represented as a percentage or decimal fraction. For example, a 10% discount is 0.10. Subtracting the discount from the original price gives the price after discount. The calculation below returns a value without changing the stored product price.

Calculate a Discounted Price

sql

1
2
3
4
5
6
SELECT
  product_name,
  unit_price,
  ROUND(unit_price * 0.10, 2) AS discount_amount,
  ROUND(unit_price * 0.90, 2) AS discounted_price
FROM inventory_items;

Output

A result showing each product's price, a 10% discount amount, and a discounted price rounded to two decimal places.

Multiplying by 0.10 calculates 10% of the price. Multiplying by 0.90 calculates the price remaining after a 10% discount.

Calculating Gross Profit and Margin

Gross profit per item can be calculated by subtracting unit cost from unit selling price. Gross margin percentage expresses that profit as a percentage of the selling price. The calculation requires a nonzero selling price to avoid division by zero. The query below uses NULLIF to turn a zero denominator into NULL instead of raising a division-by-zero error.

Calculate Profit and Margin

sql

1
2
3
4
5
6
7
8
9
10
11
SELECT
  product_name,
  unit_price,
  unit_cost,
  unit_price - unit_cost AS gross_profit,
  ROUND(
    100.0 * (unit_price - unit_cost)
      / NULLIF(unit_price, 0),
    1
  ) AS margin_percentage
FROM inventory_items;

Output

A result with product name, price, cost, gross profit, and margin percentage. Margin is NULL when unit_price is zero.

The margin formula is (selling price - cost) / selling price * 100. This is a gross margin percentage, not a markup percentage.

Understanding ROUND and Numeric Types

ROUND is useful when a result needs a defined number of decimal places. In PostgreSQL, ROUND(numeric, integer) rounds a numeric value to the requested decimal scale. For floating-point inputs, available overloads and behavior differ. For financial values, NUMERIC is generally preferable to binary floating-point types when exact decimal arithmetic is required.

Round Numeric Results

sql

1
2
3
SELECT
  ROUND(19.987::numeric, 2) AS rounded_price,
  ROUND(125.456::numeric, 1) AS rounded_score;

Output

rounded_price | rounded_score --------------+-------------- 19.99 | 125.5

The ::numeric cast makes the intended PostgreSQL numeric type explicit for ROUND with a scale argument.

Concatenating Strings with ||

String concatenation combines text values into one string. PostgreSQL supports the || operator, which can combine text columns with literal separators. This is useful for full names, formatted addresses, labels, and report fields. The operator returns NULL when an operand is NULL.

Combine First and Last Names

sql

1
2
3
SELECT
  first_name || ' ' || last_name AS full_name
FROM customers;

Output

A full_name column containing names such as Jane Doe, Robert Vance, and Carlos Santos when the component values are non-null.

The literal ' ' adds a space between the names. If either operand is NULL, the concatenated result is NULL in PostgreSQL.

Concatenating Values with CONCAT

PostgreSQL's CONCAT function joins the text representations of its arguments and ignores NULL arguments. This differs from ||, which generally produces NULL when an operand is NULL. CONCAT is convenient for some formatting tasks, but ignoring NULL can also remove separators or produce a result that needs further cleanup.

Build a Formatted Address

sql

1
2
3
SELECT
  CONCAT(city, ', ', state, ' ', postal_code) AS full_address
FROM customers;

Output

A full_address column containing formatted address text. NULL arguments are ignored by PostgreSQL CONCAT.

For fully populated values, an address may look like Seattle, WA 98101. When fields are missing, inspect the result because separators may remain even when a component is NULL.

Handling NULL with COALESCE

NULL represents a missing or unknown value. COALESCE returns the first non-null argument. Use it when a missing text value should be replaced with a meaningful fallback before concatenation. Choose a fallback that matches the meaning of the data instead of silently inventing information.

Provide a Fallback for Missing Values

sql

1
2
3
4
5
SELECT
  COALESCE(first_name, 'Unknown')
    || ' '
    || COALESCE(last_name, '') AS display_name
FROM customers;

Output

A display_name result in which a missing first_name becomes Unknown and a missing last_name becomes an empty string.

COALESCE handles NULL explicitly. Depending on the application's needs, you may prefer to display an incomplete name differently rather than use the word Unknown.

Concatenation and NULL: Compare the Results

Different concatenation methods can produce different output when values are NULL. In PostgreSQL, the || operator propagates NULL, CONCAT ignores NULL arguments, and COALESCE lets you choose a replacement. Test nullable values deliberately when building labels and addresses.

Compare NULL Handling

sql

1
2
3
4
SELECT
  'A' || NULL || 'B' AS pipe_result,
  CONCAT('A', NULL, 'B') AS concat_result,
  'A' || COALESCE(NULL, '') || 'B' AS coalesce_result;

Output

pipe_result | concat_result | coalesce_result ------------+---------------+----------------- NULL | AB | AB

The operator result is NULL because one operand is NULL. CONCAT ignores that NULL argument, while COALESCE replaces it with an empty string.

Working with Aliases in Larger Queries

Aliases make long expressions easier to understand, but an output alias is not automatically available to every clause at the same query level. In PostgreSQL, SELECT aliases can generally be used in ORDER BY, but they cannot ordinarily be referenced by WHERE in that same query level. A subquery or CTE can expose a calculated value as an input column to an outer query.

Reuse a Calculated Value with a Subquery

sql

1
2
3
4
5
6
7
8
9
10
SELECT
  item_title,
  gross_profit
FROM (
  SELECT
    product_name AS item_title,
    unit_price - unit_cost AS gross_profit
  FROM inventory_items
) AS product_profits
WHERE gross_profit > 20;

Output

Products whose calculated gross profit is greater than 20, with the output columns item_title and gross_profit.

The outer query can use gross_profit because it is a column in the subquery result.

Projection and Query Performance

Selecting fewer columns may reduce the data PostgreSQL needs to return and send to the client. It can also make an index-only scan possible if an appropriate index contains every required column and visibility conditions allow PostgreSQL to avoid fetching table rows. The optimizer decides which plan to use; fewer selected columns alone do not guarantee a faster query.

Potential Benefits of Narrower Queries

Potential benefit	Why it can help	Important limitation
Smaller result payload	Fewer values are sent to the client	Depends on selected data and row count
Less unnecessary data access	Unneeded large columns may not need to be fetched	The execution plan and storage layout matter
Index-only scan eligibility	An index may contain all requested columns	PostgreSQL visibility checks can still require heap access
Clearer application contract	The expected output is explicit	Authorization and validation remain necessary

MCQ 1: Projection

Check your understanding of column projection.

MCQ 2: Output Column Order

Check how the order of selected columns is determined.

MCQ 3: Aliases

Check what an alias does.

MCQ 4: Arithmetic

Check your understanding of a percentage discount.

MCQ 5: Gross Margin

Check the difference between gross profit and gross margin percentage.

MCQ 6: String Concatenation

Review the PostgreSQL concatenation operator.

MCQ 7: NULL Handling

Check how PostgreSQL handles NULL in common concatenation expressions.

MCQ 8: Stored Data

Check whether a calculation in SELECT changes the source table.

Guided Practice: Create a Product Report

Assume inventory_items contains product_name, unit_price, and unit_cost. Write a query that returns the product name as item_title, calculates gross_profit, and calculates a margin percentage rounded to one decimal place. Protect the division from a zero unit_price. Sort the report by gross_profit from highest to lowest.

Practice Requirements

sql

1
2
3
4
5
6
7
-- Write a SELECT query that:
-- 1. Returns product_name as item_title.
-- 2. Returns unit_price and unit_cost.
-- 3. Calculates unit_price - unit_cost as gross_profit.
-- 4. Calculates gross margin percentage.
-- 5. Uses NULLIF to avoid division by zero.
-- 6. Sorts by gross_profit descending.

Output

A report with product title, price, cost, gross profit, and margin percentage, sorted by descending gross profit.

Use an alias for the calculated values. Use NULLIF(unit_price, 0) as the denominator so a zero price produces NULL rather than a division-by-zero error.

Guided Practice Solution

Compare your query with the solution below. It returns only the requested fields and safely handles a zero selling price.

Product Report Solution

sql

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
  product_name AS item_title,
  unit_price,
  unit_cost,
  unit_price - unit_cost AS gross_profit,
  ROUND(
    100.0 * (unit_price - unit_cost)
      / NULLIF(unit_price, 0),
    1
  ) AS margin_percentage
FROM inventory_items
ORDER BY gross_profit DESC;

Output

The product report ordered from highest gross profit to lowest. A margin percentage is NULL if unit_price is zero.

The query performs projection, aliasing, arithmetic, safe division, rounding, and ordering in one SELECT statement.

Interview Questions and Answers

Review these questions to strengthen your understanding of projection, aliases, arithmetic, and string concatenation.

Lesson Summary

Column projection controls which columns and expressions appear in a query result. Aliases make those results readable, while arithmetic expressions calculate values such as discounts, profits, and margins without updating stored data. PostgreSQL supports both || and CONCAT() for string concatenation, but their NULL behavior differs. COALESCE supplies fallback values, and NULLIF helps avoid division by zero. Explicitly selecting required columns improves clarity and may improve efficiency, but actual performance depends on the query plan and database conditions.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using SELECT * instead of selecting required columns

Why it happens: The wildcard is convenient during experimentation and can be copied into production queries.

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 make the expected result clearer and can avoid unnecessary data transfer. Whether they improve execution time depends on the query plan and indexes.

#2 Using single quotes for a column alias containing spaces in PostgreSQL

Why it happens: Single quotes are confused with the double quotes used for delimited identifiers.

Incorrect Approach

sql

SELECT first_name AS 'Customer Name' FROM customers;

Correct Solution

sql

SELECT first_name AS customer_name FROM customers;
-- If a space is required:
SELECT first_name AS "Customer Name" FROM customers;

Key takeaway: Single quotes delimit string literals. Double quotes delimit identifiers. Prefer simple snake_case aliases for application-facing SQL.

#3 Assuming an alias changes the table schema

Why it happens: The result displays a new label, which can look like a permanent column.

Incorrect Approach

sql

SELECT unit_price - unit_cost AS gross_profit FROM inventory_items;
-- This does not add gross_profit to the table.

Correct Solution

sql

SELECT unit_price - unit_cost AS gross_profit
FROM inventory_items;

Key takeaway: The alias labels the calculated result only. The stored table remains unchanged.

#4 Calculating margin using cost as the denominator

Why it happens: Gross margin and markup both use profit, so their formulas can be confused.

Incorrect Approach

sql

100.0 * (unit_price - unit_cost) / NULLIF(unit_cost, 0)

Correct Solution

sql

100.0 * (unit_price - unit_cost) / NULLIF(unit_price, 0)

Key takeaway: Gross margin divides profit by selling price. The incorrect expression calculates markup percentage instead.

#5 Ignoring NULL behavior in string concatenation

Why it happens: A nullable field is treated as if it were always a regular string.

Incorrect Approach

sql

first_name || ' ' || last_name

Correct Solution

sql

COALESCE(first_name, 'Unknown') || ' ' || COALESCE(last_name, '')

Key takeaway: In PostgreSQL, the || operator propagates NULL. Choose fallback behavior deliberately because a fallback can change how missing information is presented.

#6 Assuming CONCAT always creates perfectly formatted output

Why it happens: CONCAT ignores NULL arguments, but separators are still ordinary string arguments.

Incorrect Approach

sql

CONCAT(city, ', ', state, ' ', postal_code)

Correct Solution

sql

CONCAT_WS(' ', city, state, postal_code)

Key takeaway: PostgreSQL CONCAT_WS skips NULL values after the separator argument, which can help with formatting. It does not automatically validate or normalize address components, and it skips NULL rather than necessarily skipping empty strings.

#7 Assuming selecting fewer columns guarantees an index-only scan

Why it happens: Narrow SELECT lists can make index-only scans possible, but other conditions still matter.

Incorrect Approach

sql

Assuming every query that selects two columns will use an index-only scan.

Correct Solution

sql

EXPLAIN SELECT customer_id, email FROM customers WHERE customer_id = 2;

Key takeaway: Use EXPLAIN to inspect the chosen plan. Index design, table statistics, visibility information, and query conditions all affect whether an index-only scan is possible or beneficial.

Ready for hands-on challenges?

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

Practice Arena →

Selecting Specific Columns Knowledge Check

Test your understanding with immediate feedback

Question 1 of 8

Which SQL clause determines the columns returned by a query?

Question 2 of 8

What determines the order of columns in a basic SELECT result?

Question 3 of 8

What does unit_price - unit_cost AS gross_profit do?

Question 4 of 8

Which expression calculates a 10% discount amount from unit_price?

Question 5 of 8

Which formula calculates gross margin percentage?

Question 6 of 8

Which operator concatenates text values in PostgreSQL?

Question 7 of 8

In PostgreSQL, what does CONCAT('A', NULL, 'B') return?

Question 8 of 8

Does SELECT unit_price * 0.9 AS discounted_price update unit_price?

Technical and placement interview questions

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

Q1:What is projection in SQL?

Answer:

Projection is the operation of choosing specific columns or expressions for a result. In SQL, the SELECT list controls projection.

Q2:What is the difference between projection and filtering?

Answer:

Projection chooses columns or expressions. Filtering chooses which rows qualify, usually with WHERE.

Q3:Why should production queries explicitly list columns?

Answer:

Explicit columns make the expected result clear, avoid unnecessary data transfer, and reduce accidental coupling to unrelated schema changes.

Q4:What is a column alias?

Answer:

An alias is a label for a result column or expression, commonly assigned with AS.

Q5:Does an alias rename a stored column?

Answer:

No. It changes the label in the query result, not the table definition.

Q6:Can SQL calculate values in SELECT?

Answer:

Yes. SELECT can evaluate arithmetic expressions and functions to produce calculated result columns.

Q7:Does a calculated SELECT column automatically persist?

Answer:

No. It is calculated for the query result unless a separate persistence mechanism is deliberately used.

Q8:How do you calculate gross profit?

Answer:

Subtract unit cost from unit selling price: unit_price - unit_cost.

Q9:What is the difference between gross margin and markup?

Answer:

Gross margin divides profit by selling price. Markup divides profit by cost.

Q10:How do you calculate a 15% discount amount?

Answer:

Multiply the original price by 0.15. The discounted price is the original price multiplied by 0.85.

Q11:What is the || operator used for in PostgreSQL?

Answer:

It concatenates string values. If an operand is NULL, the resulting expression is generally NULL.

Q12:What does CONCAT do with NULL in PostgreSQL?

Answer:

CONCAT ignores NULL arguments and joins the text representations of the remaining arguments.

Q13:What does COALESCE do?

Answer:

It returns the first non-null argument, allowing a query to provide an explicit fallback value.

Q14:How can you prevent division by zero in a SQL expression?

Answer:

Use NULLIF(denominator, 0). The result becomes NULL when the denominator is zero.

Q15:Does selecting fewer columns always make a query faster?

Answer:

No. It can reduce transferred data and may allow a more efficient plan, but the actual benefit depends on the query, indexes, data, and optimizer.

Q16:What is an index-only scan?

Answer:

It is a PostgreSQL scan that can return requested column values from an index without fetching heap tuples when the index contains the required data and visibility conditions permit it.

Q17:How do you create a readable full name in PostgreSQL?

Answer:

For non-null name fields, use first_name || ' ' || last_name AS full_name. If fields can be NULL, handle them explicitly with COALESCE or another suitable expression.

Key takeaways

Projection is the relational algebra operation that selects columns from a relation.
The SELECT list determines which expressions appear in the result and their output order.
AS assigns a label to a result column; it does not rename the stored table column.
Arithmetic expressions can calculate values without changing stored data.
Calculated result columns are not automatically persisted in the database.
The || operator concatenates strings in PostgreSQL, but NULL handling differs from CONCAT().
In PostgreSQL, CONCAT() ignores NULL arguments, while || generally produces NULL if an operand is NULL.
COALESCE returns the first non-null argument and can supply a fallback for nullable text.
Selecting fewer columns can reduce data transfer and may enable index-only scans when the required conditions are satisfied.
Use clear aliases and explicit columns to make application queries and reports easier to understand.

Was this lesson helpful?

Found an error? Report an issue