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.

Practice

1. What does the SQL statement SELECT name, age FROM users; do?

easy

A. It retrieves all columns from the users table.

B. It updates the name and age columns in the users table.

C. It deletes the name and age columns from the users table.

D. It retrieves only the name and age columns from the users table.

Solution

1. Step 1: Understand the SELECT clause

   The SELECT clause specifies which columns to retrieve from the table.

2. Step 2: Identify columns listed

   The columns listed are name and age, so only these will be returned.

3. Final Answer:

   It retrieves only the name and age columns from the users table. -> Option D

4. Quick Check:

   SELECT specific columns = selected columns only [OK]

Hint: SELECT lists columns to get only those columns [OK]

Common Mistakes:

• Thinking SELECT * returns specific columns
• Confusing SELECT with DELETE or UPDATE
• Not separating columns by commas

2. Which of the following is the correct syntax to select the columns 'id' and 'email' from a table named 'contacts'?

easy

A. SELECT id email FROM contacts;

B. SELECT id, email FROM contacts;

C. SELECT (id, email) FROM contacts;

D. SELECT id; email FROM contacts;

Solution

1. Step 1: Check column separation syntax

   Columns must be separated by commas without parentheses.

2. Step 2: Identify correct option

   SELECT id, email FROM contacts; uses commas correctly: SELECT id, email FROM contacts;

3. Final Answer:

   SELECT id, email FROM contacts; -> Option B

4. Quick Check:

   Columns separated by commas = correct syntax [OK]

Hint: Separate columns with commas, no parentheses [OK]

Common Mistakes:

• Omitting commas between columns
• Using parentheses around columns
• Using semicolons between columns

3. Given the table products with columns product_id, name, and price, what will be the output of this query?

SELECT name, price FROM products WHERE price < 50;

medium

A. All columns for products priced below 50

B. An error because price is not selected

C. Only the name and price columns for products priced below 50

D. Only the product_id column for products priced below 50

Solution

1. Step 1: Understand SELECT columns

   The query selects only name and price columns.

2. Step 2: Apply WHERE condition

   Only rows where price is less than 50 are included.

3. Final Answer:

   Only the name and price columns for products priced below 50 -> Option C

4. Quick Check:

   SELECT specific columns + WHERE filters rows [OK]

Hint: SELECT columns limits output columns, WHERE filters rows [OK]

Common Mistakes:

• Expecting all columns to be returned
• Thinking WHERE changes columns selected
• Assuming error if column not in SELECT

4. Identify the error in this SQL query:

SELECT name age FROM employees;

medium

A. Missing comma between columns name and age

B. Table name employees is incorrect

C. SELECT cannot be used without WHERE

D. No error, query is correct

Solution

1. Step 1: Check column separation

   Columns must be separated by commas; here name and age are not separated.

2. Step 2: Confirm table name and syntax

   Table name employees is valid and WHERE is optional.

3. Final Answer:

   Missing comma between columns name and age -> Option A

4. Quick Check:

   Columns need commas between them [OK]

Hint: Always separate columns with commas [OK]

Common Mistakes:

• Forgetting commas between columns
• Thinking WHERE is mandatory
• Assuming table name is wrong without evidence

5. You have a table orders with columns order_id, customer_id, order_date, and total. You want to list only the order_id and total for orders made in 2023. Which query is correct?

hard

A. SELECT order_id, total FROM orders WHERE order_date LIKE '2023%';

B. SELECT order_id total FROM orders WHERE order_date = 2023;

C. SELECT order_id, total FROM orders WHERE order_date = '2023';

D. SELECT * FROM orders WHERE order_date LIKE '2023%';

Solution

1. Step 1: Select specific columns

   We want only order_id and total, so SELECT order_id, total is correct.

2. Step 2: Filter orders from 2023

   Using WHERE order_date LIKE '2023%' matches dates starting with 2023.

3. Step 3: Check other options

   SELECT order_id total FROM orders WHERE order_date = 2023; misses comma and uses wrong date filter; SELECT order_id, total FROM orders WHERE order_date = '2023'; uses exact match which won't work for full dates; SELECT * FROM orders WHERE order_date LIKE '2023%'; selects all columns, not specific ones.

4. Final Answer:

   SELECT order_id, total FROM orders WHERE order_date LIKE '2023%'; -> Option A

5. Quick Check:

   Select columns + LIKE filter for year = correct query [OK]

Hint: Use LIKE '2023%' to filter year, separate columns with commas [OK]

Common Mistakes:

• Forgetting commas between columns
• Using = instead of LIKE for partial date match
• Selecting all columns instead of specific ones