Selecting specific columns in MySQL - Deep Dive

Overview - Selecting specific columns

What is it?

Selecting specific columns means choosing only certain pieces of information from a table in a database instead of taking everything. It helps you get just the data you need, like picking only the names and phone numbers from a contact list. This makes your results smaller and easier to understand. You do this using a simple command that lists the columns you want.

Why it matters

Without selecting specific columns, you would always get all the data from a table, which can be slow and confusing if the table has many columns. It wastes time and computer resources. By choosing only what you need, you get faster answers and clearer information, which helps in making decisions or building apps that work well.

Where it fits

Before learning this, you should know what a database and a table are, and how to write a basic query to get all data. After this, you can learn how to filter rows, sort data, and combine data from multiple tables to answer more complex questions.

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 box of mixed fruits, but you only want apples and bananas. Instead of taking the whole box, you pick just those fruits you want. Selecting columns works the same way with data.

┌───────────────┐
│   Table: People   │
├───────────────┤
│ id │ name │ age │ city │
├────┼──────┼─────┼──────┤
│ 1  │ Anna │  30 │ NY   │
│ 2  │ Bob  │  25 │ LA   │
│ 3  │ Cara │  28 │ SF   │
└────┴──────┴─────┴──────┘

SELECT name, city FROM People;

Result:
┌──────┬──────┐
│ name │ city │
├──────┼──────┤
│ Anna │ NY   │
│ Bob  │ LA   │
│ Cara │ SF   │
└──────┴──────┘

Build-Up - 7 Steps

1

Foundation

Understanding tables and columns

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

A database stores data in tables, which look like grids. Each column holds one type of information, like names or ages. Each row is one record, like one person’s data. Knowing this helps you pick which columns to get.

Result

You understand that tables have columns and rows, and columns represent specific data types.

Understanding the structure of tables and columns is the base for selecting specific data you want.

2

Foundation

Basic SELECT * query

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

The simplest query to get data is SELECT * FROM table_name; This returns every column and every row. It’s like taking the whole box of fruits without picking specific ones.

Result

You get all columns and rows from the table.

Knowing how to get all data first helps you appreciate why selecting specific columns is useful.

3

Intermediate

Selecting specific columns syntax

🤔Before reading on: do you think you can list multiple columns separated by commas in a SELECT statement? Commit to your answer.

Concept: Learn the syntax to select only certain columns by naming them.

Instead of *, you write SELECT column1, column2 FROM table_name; For example, SELECT name, age FROM People; This returns only the name and age columns for all rows.

Result

The query returns only the chosen columns, making the result smaller and focused.

Knowing the syntax to pick columns lets you control what data you get, improving efficiency and clarity.

4

Intermediate

Using column aliases for clarity

🤔Before reading on: do you think you can rename columns in the output using AS? Commit to your answer.

Concept: Learn how to rename columns in the result to make them clearer or friendlier.

You can write SELECT name AS 'Full Name', age AS 'Years' FROM People; This changes the column headers in the output without changing the data stored.

Result

The output shows columns with new names, making it easier to understand.

Using aliases helps make query results more readable, especially when column names are unclear or technical.

5

Intermediate

Selecting columns with expressions

🤔Before reading on: do you think you can perform calculations or combine columns in a SELECT statement? Commit to your answer.

Concept: Learn how to use simple calculations or combine columns when selecting data.

You can write SELECT name, age + 1 AS 'Age Next Year' FROM People; This adds 1 to the age column in the output. Or SELECT CONCAT(name, ' from ', city) AS 'Description' FROM People; to combine text.

Result

The query returns columns with calculated or combined values.

Knowing you can manipulate data in the SELECT clause expands what you can get from a query beyond raw stored data.

6

Advanced

Selecting columns with functions

🤔Before reading on: do you think you can use built-in functions like UPPER() or COUNT() in SELECT? Commit to your answer.

Concept: Learn how to use SQL functions to transform or summarize data in selected columns.

You can write SELECT UPPER(name) AS 'Name Uppercase' FROM People; to change text case. Or SELECT COUNT(age) AS 'Total People' FROM People; to count rows with age data.

Result

The output shows transformed or aggregated data based on functions.

Using functions in SELECT lets you get meaningful summaries or formatted data directly from the database.

7

Expert

Impact of selecting columns on performance

🤔Before reading on: do you think selecting fewer columns always makes queries faster? Commit to your answer.

Concept: Understand how choosing specific columns affects query speed and resource use in real databases.

When you select fewer columns, the database reads and sends less data, which is faster and uses less memory. However, if columns are indexed or stored differently, sometimes selecting certain columns can be faster or slower. Also, selecting unnecessary columns can slow down applications and networks.

Result

You learn that careful column selection improves performance but knowing database internals helps optimize further.

Understanding performance impact guides writing efficient queries and designing better databases.

Under the Hood

When you run a SELECT query with specific columns, the database engine reads only those columns from the storage files or memory. It skips other columns, reducing data transfer and processing. The engine uses metadata to map column names to storage locations. If columns are indexed, the engine may use indexes to speed up access. The result set is built with only requested columns and sent back to the user.

Why designed this way?

Databases were designed to handle large amounts of data efficiently. Selecting specific columns reduces unnecessary data movement and speeds up queries. Early database systems used fixed schemas, so selecting columns was a natural way to control output. Alternatives like always returning all data would waste resources and slow down systems.

┌───────────────┐
│   User Query  │
│ SELECT name, age FROM People │
└───────┬───────┘
        │
        ▼
┌───────────────────────────┐
│ Database Engine            │
│ - Parse query             │
│ - Identify columns needed │
│ - Access storage for name │
│   and age columns         │
│ - Skip other columns      │
└─────────────┬─────────────┘
              │
              ▼
┌───────────────────────────┐
│ Result Set:               │
│ name | age               │
│ Anna | 30                │
│ Bob  | 25                │
│ Cara | 28                │
└───────────────────────────┘

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 order they appear in the table.

Reality: While usually true, some database systems or views may reorder columns, so relying on SELECT * order is risky.

Why it matters: Assuming column order can cause bugs when processing results, especially in code expecting a fixed order.

Quick: Does selecting more columns always slow down your query? Commit to yes or no.

Common Belief: Selecting more columns always makes queries slower.

Reality: Not always. If columns are stored together or indexed, sometimes selecting more columns has little impact. Also, network speed and client processing matter.

Why it matters: Over-optimizing by removing columns without measuring can waste time and cause premature optimization.

Quick: Can you use column aliases to rename columns in the actual table? Commit to yes or no.

Common Belief: Using AS renames the column permanently in the table.

Reality: Aliases only rename columns in the query result, not in the stored table schema.

Why it matters: Confusing aliases with schema changes can lead to wrong assumptions about data structure.

Quick: Does selecting specific columns prevent you from filtering rows based on other columns? Commit to yes or no.

Common Belief: You can only filter rows based on columns you select.

Reality: You can filter rows using any column in the WHERE clause, even if you don't select that column.

Why it matters: Believing this limits query flexibility and leads to unnecessary data retrieval.

Expert Zone

1

Selecting columns that are part of an index can speed up queries because the database can use the index without reading the full table data.

2

In some databases, selecting large text or binary columns (BLOBs) can be expensive; omitting them when not needed improves performance significantly.

3

Using SELECT * in production code is discouraged because schema changes can break applications that expect a fixed column set.

When NOT to use

Avoid selecting specific columns when you need all data for backup or full export purposes. Also, if you need to perform operations involving all columns, selecting specific ones may be limiting. In those cases, use SELECT * or export tools designed for full data extraction.

Production Patterns

In real systems, developers write queries selecting only needed columns to reduce network load and speed up APIs. Column aliases are used to match frontend naming conventions. Queries often combine selecting columns with filtering and joining tables to get precise datasets for reports or user views.

Connections

Data Filtering

Builds-on

Selecting specific columns works hand-in-hand with filtering rows to get exactly the data you want, making queries efficient and focused.

API Design

Same pattern

Just like selecting specific columns in a database query, APIs often let clients request only certain fields to reduce data transfer and improve performance.

Minimalism in Design

Opposite pattern

Selecting specific columns reflects the principle of minimalism by avoiding unnecessary data, similar to how minimalism in art or writing removes excess to highlight the important.

Common Pitfalls

#1 Selecting columns but misspelling a column name.

Wrong approach:

SELECT nmae, age FROM People;

Correct approach:

SELECT name, age FROM People;

Root cause:

Typos in column names cause syntax errors or unexpected results because the database cannot find the requested column.

#2 Using SELECT * when only a few columns are needed.

Wrong approach:

SELECT * FROM People;

Correct approach:

SELECT name, city FROM People;

Root cause:

Not limiting columns wastes resources and slows down queries, especially on large tables.

#3 Trying to rename columns in the table using AS in SELECT.

Wrong approach:

SELECT name AS full_name FROM People; -- expecting table column to rename

Correct approach:

Use ALTER TABLE to rename columns permanently; AS only renames output columns.

Root cause:

Confusing query output formatting with schema changes leads to wrong expectations.

Key Takeaways

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

You write the column names separated by commas after SELECT to choose them.

Column aliases rename columns in the output but do not change the stored data.

Selecting fewer columns usually improves performance but understanding database internals helps optimize further.

Filtering rows and selecting columns are separate but complementary ways to control query results.

Practice

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

easy

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

B. It retrieves all columns from 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 statement

   The SELECT keyword is used to choose specific columns from a table.

2. Step 2: Identify the columns listed

   The columns listed after SELECT are name and age, so only these will be retrieved.

3. Final Answer:

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

4. Quick Check:

   Select specific columns = name, age [OK]

Hint: SELECT columns by listing names separated by commas [OK]

Common Mistakes:

• Thinking SELECT * returns specific columns
• Confusing SELECT with DELETE or UPDATE
• Forgetting commas between column names

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

easy

A. SELECT id email FROM customers;

B. SELECT id, email FROM customers;

C. SELECT (id, email) FROM customers;

D. SELECT * FROM customers WHERE id, email;

Solution

1. Step 1: Recall correct SELECT syntax

   Columns must be separated by commas without parentheses.

2. Step 2: Check each option

   SELECT id, email FROM customers; uses commas correctly: SELECT id, email FROM customers;.

3. Final Answer:

   SELECT id, email FROM customers; -> Option B

4. Quick Check:

   Columns separated by commas = SELECT id, email FROM customers; [OK]

Hint: Use commas between columns, no parentheses [OK]

Common Mistakes:

• Omitting commas between columns
• Using parentheses around columns
• Misusing WHERE clause for column selection

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 > 100;

medium

A. Only the name and price of products with price greater than 100.

B. All columns for products with price greater than 100.

C. Only the product_id and price of products with price greater than 100.

D. Syntax error due to missing commas.

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only name and price columns.

2. Step 2: Analyze the WHERE clause

   It filters rows where price > 100.

3. Final Answer:

   Only the name and price of products with price greater than 100. -> Option A

4. Quick Check:

   Select specific columns with condition = Only the name and price of products with price greater than 100. [OK]

Hint: SELECT columns listed, WHERE filters rows [OK]

Common Mistakes:

• Assuming all columns are returned
• Confusing WHERE with column selection
• Thinking missing commas cause error here

4. Identify the error in this query:

SELECT id name FROM employees;

medium

A. SELECT keyword is misspelled.

B. Table name is incorrect.

C. Missing comma between column names.

D. No error, query is correct.

Solution

1. Step 1: Check column list syntax

   Columns must be separated by commas. Here, id and name are missing a comma.

2. Step 2: Verify other parts

   Table name employees and SELECT keyword are correct.

3. Final Answer:

   Missing comma between column names. -> Option C

4. Quick Check:

   Comma missing between columns = Missing comma between column names. [OK]

Hint: Separate columns with commas to avoid syntax errors [OK]

Common Mistakes:

• Forgetting commas between columns
• Assuming space works as separator
• Misreading error as table or keyword issue

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

hard

A. SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023;

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

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

D. SELECT * FROM orders WHERE order_date = 2023;

Solution

1. Step 1: Select specific columns

   The query must select order_id and total only, so SELECT order_id, total is correct.

2. Step 2: Filter orders from 2023

   Using WHERE YEAR(order_date) = 2023 correctly filters orders placed in 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 LIKE '2023%'; uses LIKE which may work but is less precise. SELECT * FROM orders WHERE order_date = 2023; selects all columns, not specific ones.

4. Final Answer:

   SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023; -> Option A

5. Quick Check:

   Select columns + correct year filter = SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023; [OK]

Hint: Use YEAR() to filter dates, commas to select columns [OK]

Common Mistakes:

• Omitting commas between columns
• Using wrong date filter syntax
• Selecting all columns instead of specific ones