SELECT specific columns in SQL - Deep Dive

Overview - SELECT specific columns

What is it?

SELECT specific columns is a way to ask a database to show only certain pieces of information from a table, instead of everything. It helps you focus on the exact data you need by naming the columns you want. This makes the results easier to read and faster to get. For example, you can ask for just the names and phone numbers from a list of contacts.

Why it matters

Without the ability to select specific columns, you would always get all the data from a table, which can be overwhelming and slow. This wastes time and resources, especially with large databases. Selecting specific columns helps you get only what matters, making your work more efficient and your results clearer.

Where it fits

Before learning this, you should understand what a database table is and how data is organized in rows and columns. After mastering selecting specific columns, you can learn how to filter rows with WHERE, sort results with ORDER BY, and combine data from multiple tables with JOIN.

Mental Model

Core Idea

Selecting specific columns is like choosing only the fields you want from a form instead of taking the whole form.

Think of it like...

Imagine you have a big filing cabinet full of folders, each folder has many papers (columns). Instead of pulling out the entire folder, you pick only the papers you need, like just the invoices or just the receipts.

┌─────────────┐
│   Table     │
├─────────────┤
│ ID          │
│ Name        │
│ Email       │
│ Phone       │
│ Address     │
└─────────────┘

SELECT Name, Phone
  ↓
┌─────────────┐
│ Name        │
│ Phone       │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding database tables and columns

Concept: Learn what tables and columns are in a database.

A database stores data in tables. Each table looks like a grid with rows and columns. Columns are the categories or types of data, like Name or Age. Rows are the individual records, like one person's data.

Result

You can identify what data is stored and how it is organized in a table.

Knowing the structure of tables and columns is essential before you can ask for specific data.

2

Foundation

Basic SELECT statement to get all data

Concept: Learn how to get all data from a table using SELECT *.

The simplest way to get data is to use SELECT * FROM table_name; This means 'give me everything from this table.' For example, SELECT * FROM employees; returns all columns and rows.

Result

You get a full list of all data in the table.

Starting with SELECT * helps you see the full data before choosing specific columns.

3

Intermediate

Selecting specific columns by name

🤔Before reading on: do you think you can list multiple columns separated by commas in SELECT? Commit to yes or no.

Concept: Learn how to specify which columns to get by naming them in the SELECT statement.

Instead of SELECT *, you write SELECT column1, column2 FROM table_name; For example, SELECT Name, Email FROM employees; returns only the Name and Email columns for all rows.

Result

The output shows only the chosen columns, making data easier to read and faster to retrieve.

Selecting specific columns reduces clutter and improves query performance by fetching only needed data.

4

Intermediate

Using column aliases to rename output

🤔Before reading on: do you think you can change the column names in the output using SQL? Commit to yes or no.

Concept: Learn how to rename columns in the result using AS to make output clearer.

You can rename columns in the output by adding AS new_name after the column. For example, SELECT Name AS EmployeeName, Email AS ContactEmail FROM employees; changes the column headers in the result.

Result

The output columns have new, clearer names without changing the actual table.

Column aliases help make query results more understandable, especially when sharing with others.

5

Intermediate

Selecting columns with expressions

🤔Before reading on: can you select a column plus some calculation in SQL? Commit to yes or no.

Concept: Learn how to select columns combined with calculations or functions.

You can select columns with added expressions, like SELECT Price, Price * 0.9 AS DiscountedPrice FROM products; This shows the original price and a calculated discounted price.

Result

The output includes both original and calculated columns, useful for quick data insights.

Selecting columns with expressions lets you get more value from data without changing the table.

6

Advanced

Selecting columns from multiple tables

🤔Before reading on: do you think you can select columns from two tables at once? Commit to yes or no.

Concept: Learn how to select specific columns when joining tables together.

When combining tables with JOIN, you specify columns with table names or aliases, like SELECT e.Name, d.DepartmentName FROM employees e JOIN departments d ON e.DepartmentID = d.ID; This gets employee names and their department names.

Result

The output shows columns from both tables combined in one result.

Selecting specific columns from joined tables keeps results focused and avoids confusion from duplicate column names.

7

Expert

Performance impact of selecting columns

🤔Before reading on: do you think selecting fewer columns always makes queries faster? Commit to yes or no.

Concept: Understand how selecting specific columns affects query speed and resource use.

Selecting fewer columns reduces the amount of data the database reads and sends, which can speed up queries, especially on large tables. However, some databases optimize queries internally, so the difference may vary. Also, selecting unnecessary columns wastes bandwidth and memory.

Result

Efficient queries run faster and use fewer resources, improving overall system performance.

Knowing the performance impact guides writing better queries and designing efficient applications.

Under the Hood

When you run a SELECT query specifying columns, the database engine reads only those columns from the storage layer. It uses the table schema to locate the columns and fetches data row by row. This reduces data transfer and processing compared to fetching all columns. Internally, the engine creates a result set with only the requested columns, which it returns to the user.

Why designed this way?

Databases were designed to handle large amounts of data efficiently. Allowing users to select specific columns avoids unnecessary data retrieval, saving time and resources. Early database systems optimized for this to support many users and large datasets. Alternatives like always returning all columns would be slow and wasteful.

┌───────────────┐
│   Query       │
│ SELECT Name,  │
│ Phone FROM    │
│ contacts      │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Database      │
│ Engine        │
│ Reads only    │
│ Name, Phone   │
│ columns       │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Result Set    │
│ Name | Phone  │
│ ...  | ...    │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does SELECT * always return columns in the same order as the table? Commit to yes or no.

Common Belief: SELECT * returns columns in the exact order they appear in the table schema.

Reality: While usually true, some databases may reorder columns internally or when views are involved, so the order is not guaranteed.

Why it matters: Relying on column order can cause bugs when processing results, especially if code assumes a fixed order.

Quick: If you select columns not in the table, will the query run? Commit to yes or no.

Common Belief: You can select any column name you want, even if it doesn't exist in the table.

Reality: The database will return an error if you try to select columns that do not exist in the table.

Why it matters: Trying to select non-existent columns causes query failures and interrupts workflows.

Quick: Does selecting fewer columns always make queries much faster? Commit to yes or no.

Common Belief: Selecting fewer columns always drastically improves query speed.

Reality: While selecting fewer columns often helps, some databases optimize queries so the speed difference may be small, especially on small tables or with indexes.

Why it matters: Overestimating performance gains can lead to premature optimization or ignoring other bottlenecks.

Quick: Can you use column aliases to change the actual table column names? Commit to yes or no.

Common Belief: Using AS to rename columns in SELECT changes the column names in the database table permanently.

Reality: Column aliases only rename columns in the query result, not in the actual table schema.

Why it matters: Confusing aliases with schema changes can cause misunderstandings about data structure and lead to errors.

Expert Zone

1

Some databases support selecting columns using expressions or functions that do not exist as table columns, which can be used for dynamic calculations.

2

When selecting columns from joined tables, using table aliases is critical to avoid ambiguity and improve query readability.

3

Selecting columns with large data types (like BLOBs or TEXT) can impact performance more than the number of columns, so be mindful of data types.

When NOT to use

Selecting specific columns is not suitable when you need to export or analyze the entire dataset, or when the table schema changes frequently and you want to avoid missing new columns. In such cases, SELECT * or dynamic queries might be better.

Production Patterns

In real-world systems, selecting specific columns is combined with filtering (WHERE), sorting (ORDER BY), and pagination (LIMIT) to efficiently retrieve only the needed data for user interfaces or reports. Column aliases are used to make results user-friendly, and joins are carefully crafted to avoid redundant data.

Connections

Data Filtering with WHERE

Builds-on

Selecting specific columns focuses on which data to show, while WHERE focuses on which rows to include; combining both gives precise control over query results.

API Data Requests

Similar pattern

Just like selecting specific columns in SQL, APIs often allow clients to request only certain fields to reduce data transfer and improve performance.

Minimalism in Design

Conceptual parallel

Selecting only what is necessary in SQL mirrors the design principle of minimalism, where removing excess leads to clearer, more efficient outcomes.

Common Pitfalls

#1 Selecting columns without commas between them

Wrong approach:

SELECT Name Email FROM employees;

Correct approach:

SELECT Name, Email FROM employees;

Root cause:

Forgetting that SQL requires commas to separate column names in the SELECT clause.

#2 Using column names that do not exist in the table

Wrong approach:

SELECT Name, PhoneNumber FROM employees;

Correct approach:

SELECT Name, Phone FROM employees;

Root cause:

Confusing or misremembering the exact column names defined in the table schema.

#3 Assuming column aliases rename table columns permanently

Wrong approach:

SELECT Name AS EmployeeName FROM employees; -- then expecting the table column to be renamed

Correct approach:

SELECT Name AS EmployeeName FROM employees; -- alias only affects output, not table schema

Root cause:

Misunderstanding the difference between query output formatting and actual database schema changes.

Key Takeaways

Selecting specific columns lets you retrieve only the data you need, making queries faster and results clearer.

You list column names separated by commas after SELECT to specify which columns to get.

Column aliases rename columns in the output without changing the database table.

Selecting columns carefully is a foundation for writing efficient and readable SQL queries.

Understanding how selecting columns works helps you combine it with filtering, sorting, and joining for powerful data retrieval.