Chapter 6: Retrieving Data – The SELECT Statement

You’ve successfully set up your database, defined its structure with DDL, and populated it with data using INSERT INTO. But what’s the point of storing data if you can’t access it? This is where the SELECT statement comes in. The SELECT statement is by far the most frequently used SQL command for web developers because it’s how your web application fetches all the dynamic content to display to users.

In this chapter, you’ll learn the fundamental syntax of the SELECT statement, how to retrieve all columns, how to select specific columns, and how to use aliases to make your results more readable. This is the cornerstone of querying data from your relational database.

The Purpose of SELECT

The SELECT statement is a DML (Data Manipulation Language) command used to query the database and retrieve data that matches specified criteria. It’s the “read” operation of CRUD (Create, Read, Update, Delete).

When you execute a SELECT statement, the database returns a result set, which is essentially a temporary table containing the rows and columns that match your query. This result set is then used by your web application (e.g., PHP code) to display information on a webpage.

Basic Structure of SELECT

The most basic form of a SELECT statement includes:

1. The SELECT keyword, followed by the columns you want to retrieve.
2. The FROM keyword, followed by the table you want to retrieve data from.

SQL Syntax & Examples

Let’s work with our scriptbuzz_db and the data we inserted in Chapter 5. Ensure your database is selected:

USE scriptbuzz_db;

USE scriptbuzz_db;

1. Retrieving All Columns (SELECT *)

The asterisk (*) is a wildcard character that means “all columns.” This is the quickest way to see all data in a table.

Syntax:

SELECT * FROM table_name;

SELECT * FROM table_name;

Example 1: Get all data from users

SELECT * FROM users;

SELECT * FROM users;

Expected Output (example):

What it does: Returns every column and every row from the users table.

Example 2: Get all data from products

SELECT * FROM products;

SELECT * FROM products;

Expected Output (example):

Best Practice: Avoid SELECT * in Production Code

While SELECT * is convenient for quick exploration during development, it’s generally a bad practice for production web applications.

• Performance: Retrieving unnecessary columns wastes database resources and network bandwidth, especially for tables with many columns or large text/binary data.
• Maintainability: If the table structure changes (e.g., a new column is added), your application might unexpectedly receive new data, potentially breaking existing code.
• Security: You might expose sensitive data that your application doesn’t actually need.

Always explicitly list the columns you need in your SELECT statements.

2. Retrieving Specific Columns

To retrieve only the columns you’re interested in, list their names after the SELECT keyword, separated by commas.

Syntax:

SELECT column1, column2, ... FROM table_name;

SELECT column1, column2, ... FROM table_name;

Example 3: Get only usernames and emails from users

SELECT username, email FROM users;

SELECT username, email FROM users;

Expected Output (example):

Example 4: Get product names and prices

SELECT name, price FROM products;

SELECT name, price FROM products;

Expected Output (example):

3. Using Aliases with AS

Aliases allow you to give a temporary, more readable name to a column or a table in your query’s result set. This is useful when column names are long, or when you are combining columns.

Syntax:

SELECT column_name AS alias_name FROM table_name;
-- OR (AS is optional in MySQL/MariaDB)
SELECT column_name alias_name FROM table_name;

SELECT column_name AS alias_name FROM table_name;
-- OR (AS is optional in MySQL/MariaDB)
SELECT column_name alias_name FROM table_name;

Example 5: Alias username to User Name

SELECT username AS "User Name", email AS "User Email" FROM users;
-- Or simply: SELECT username "User Name", email "User Email" FROM users;

SELECT username AS "User Name", email AS "User Email" FROM users;
-- Or simply: SELECT username "User Name", email "User Email" FROM users;

Note: Use double quotes or backticks (`) around aliases if they contain spaces or special characters. Otherwise, quotes are optional.

Expected Output (example):

What it does: The column in the result set will be named “User Name” instead of “username,” and “User Email” instead of “email.”

Example 6: Concatenating Columns and Aliasing

You can combine data from multiple columns into one in the result set using functions like CONCAT().

SELECT CONCAT(name, ' - $', price) AS "Product Info" FROM products;

SELECT CONCAT(name, ' - $', price) AS "Product Info" FROM products;

What it does: Creates a single column in the result set named “Product Info” that combines the product’s name and price, formatted as “Name – $Price”.

Expected Output (example):

Tips

• Explicit Column Listing: Always list columns explicitly in production code for performance, maintainability, and clarity.
• Meaningful Aliases: Use aliases to make your result set more understandable, especially when dealing with complex queries or calculated columns.
• Case Sensitivity: In MySQL/MariaDB, table and column names are generally case-insensitive on Windows, but can be case-sensitive on Linux (depending on configuration). It’s best practice to stick to one case (e.g., lowercase with underscores) for consistency. SQL keywords (SELECT, FROM) are not case-sensitive.
• Semicolon: While often optional for single queries in tools like phpMyAdmin, it’s good practice to end each SQL statement with a semicolon (;). It’s mandatory if you run multiple statements in one batch.

Notes:

• The SELECT statement is used to retrieve data from database tables.
• SELECT * FROM table_name; retrieves all columns and all rows. Use sparingly in production.
• SELECT column1, column2 FROM table_name; retrieves only specified columns. This is the preferred method.
• AS keyword is used to give temporary, more descriptive aliases to columns in the result set (SELECT column AS alias_name).
• Aliasing improves readability of query results.
• Always be explicit with column names for better performance and maintainability.

Small Exercise

Using your scriptbuzz_db:

1. Write a query to retrieve only the product_id and name of all products.
2. Write a query to retrieve the username and created_at date for all users. Alias created_at to Registration Date.
3. If you created the orders table, write a query to display the order_id, total_amount, and status of all orders.
4. If you created the posts table, write a query to display the title of all posts, aliasing title to Blog Post Title.

You’re now able to pull data out of your database! In the next chapter, we’ll learn how to get specific data by filtering your results.

🚀 Explore Popular Learning Tracks

Learn Python

Learn AWS

Learn Git & GitHub