KAHIBARO
Discord Login Register ☰
☰

10.4. SELECT

Table of Contents

Basic idea of `SELECT`

The SELECT statement reads data from a table and returns it as a result set.

Very simple form:

sql 
SELECT column1, column2
FROM table_name;

Example, imagine a table:

users:

idusernameemailage
1alicealice@example.com25
2bobbob@example.com31
3carolcarol@example.com29

Query:

sql 
SELECT id, username
FROM users;

Result:

idusername
1alice
2bob
3carol

Core rule:
SELECT defines what columns you want, FROM defines which table(s) you read from.

You can select all columns with *:

sql 
SELECT *
FROM users;

This returns every column in users. This is handy while learning, but in real applications you usually avoid SELECT * and specify only the columns you need.

Column lists and aliases

Selecting specific columns

You can list any number of columns:

sql 
SELECT username, email, age
FROM users;

Order matters: the result columns appear in the same order as in the SELECT list, not necessarily in the table definition order.

You can even repeat a column:

sql 
SELECT username, age, age
FROM users;

Result has three columns, the last two are both age.

This is rarely useful in real systems, but it shows that the SELECT list is independent from the table structure.

Column aliases with `AS`

Aliases rename columns in the result, not in the table. They are useful when:

Syntax:

sql 
SELECT column_name AS alias_name
FROM table_name;

Examples:

sql 
SELECT
  username AS user_name,
  email   AS contact_email
FROM users;

Result:

user_namecontact_email
alicealice@example.com
bobbob@example.com
carolcarol@example.com

AS is optional, you can write:

sql 
SELECT
  username user_name,
  email   contact_email
FROM users;

Both forms are equivalent. Using AS is usually clearer and easier to read.

Aliases can also be used for computed columns:

sql 
SELECT
  username,
  age + 1 AS age_next_year
FROM users;

Result:

usernameage_next_year
alice26
bob32
carol30

Important: Aliases change only the column names in the query output, they do not change the actual column names in the database.

Selecting all columns with `*`

* means “all columns” from the tables in the FROM clause.

sql 
SELECT *
FROM users;

This returns all columns of users.

You can combine * with extra computed columns:

sql 
SELECT
  *,
  age + 10 AS age_in_10_years
FROM users;

Result:

idusernameemailageage_in_10_years
1alicealice@example.com2535
2bobbob@example.com3141
3carolcarol@example.com2939

Why `SELECT *` can be a problem in backends

For learning, SELECT * is fine. In real applications, it can be harmful:

Better to explicitly list columns:

sql 
SELECT id, username, email, age
FROM users;

Constants and simple expressions

You are not limited to table columns in the SELECT list. You can add:

Adding constant values

sql 
SELECT
  username,
  'ACTIVE' AS status
FROM users;

Result:

usernamestatus
aliceACTIVE
bobACTIVE
carolACTIVE

Every row gets the same constant value in the status column.

You can also use numeric constants:

sql 
SELECT
  username,
  age,
  18 AS legal_age_limit
FROM users;

Result:

usernameagelegal_age_limit
alice2518
bob3118
carol2918

Arithmetic expressions

Basic arithmetic works like in most programming languages:

OperatorMeaning
+addition
-subtraction
*multiplication
/division

Examples:

sql 
SELECT
  username,
  age,
  age + 5    AS age_in_5_years,
  age * 12   AS age_in_months,
  age / 10.0 AS age_div_10
FROM users;

Result:

usernameageage_in_5_yearsage_in_monthsage_div_10
alice25303002.5
bob31363723.1
carol29343482.9

You can mix column names and constants in expressions.

Tip: Always give expressions an alias. Otherwise the column name in the result is the expression text itself, which is hard to use and read.

Removing duplicate rows with `DISTINCT`

By default, SELECT returns one row for each matching row in the table, even if several rows have the same values in the selected columns.

DISTINCT removes duplicate rows from the result.

Syntax:

sql 
SELECT DISTINCT column1, column2, ...
FROM table_name;

Imagine a simple table:

orders:

iduser_idstatus
11pending
22shipped
31shipped
43pending
52shipped

Without DISTINCT:

sql 
SELECT status
FROM orders;

Result:

status
pending
shipped
shipped
pending
shipped

With DISTINCT:

sql 
SELECT DISTINCT status
FROM orders;

Result:

status
pending
shipped

If you select more than one column, DISTINCT works on the combination of those columns.

Example:

sql 
SELECT DISTINCT user_id, status
FROM orders;

Result:

user_idstatus
1pending
2shipped
1shipped
3pending

Even though status = 'pending' appears multiple times, the pair (3, 'pending') is distinct from (1, 'pending').

Rule:
DISTINCT keeps only unique combinations of all selected columns together, not each column separately.

Basic filtering with `WHERE` (preview)

Full details of WHERE belong in the WHERE chapter, but it is very hard to talk about SELECT without touching it at all. You will see only simple examples here.

WHERE filters rows before they are returned. Only rows that satisfy the condition are included.

General pattern:

sql 
SELECT column_list
FROM table_name
WHERE condition;

Examples:

sql 
-- Users older than 25
SELECT id, username, age
FROM users
WHERE age > 25;

Result (with our sample data):

idusernameage
2bob31
3carol29

Another example:

sql 
-- Orders that are shipped
SELECT id, user_id, status
FROM orders
WHERE status = 'shipped';

Result:

iduser_idstatus
22shipped
31shipped
52shipped

Later, in the WHERE chapter, you will learn about more complex conditions with AND, OR, IN, LIKE, and working with NULL.

Ordering results with `ORDER BY` (preview)

Again, full details belong in the ORDER BY chapter, but you need a quick idea here.

ORDER BY sorts the result rows.

General pattern:

sql 
SELECT column_list
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Example:

sql 
SELECT id, username, age
FROM users
ORDER BY age;

Result:

idusernameage
1alice25
3carol29
2bob31

Now reverse the order:

sql 
SELECT id, username, age
FROM users
ORDER BY age DESC;

Result:

idusernameage
2bob31
3carol29
1alice25

You can also sort by multiple columns:

sql 
SELECT id, username, age
FROM users
ORDER BY age DESC, username ASC;

This sorts first by age descending, and for equal ages by username ascending.

Limiting the number of rows (`LIMIT` / `OFFSET`) (preview)

Limiting belongs more naturally with pagination, but it is very commonly used with simple SELECT queries.

In many database systems, you can restrict the number of rows using LIMIT.

General pattern:

sql 
SELECT column_list
FROM table_name
ORDER BY some_column
LIMIT n;

n is the maximum number of rows you want.

Example:

sql 
-- Only the first 2 users when ordered by id
SELECT id, username
FROM users
ORDER BY id
LIMIT 2;

Result:

idusername
1alice
2bob

You can skip some rows using OFFSET:

sql 
SELECT id, username
FROM users
ORDER BY id
LIMIT 2 OFFSET 1;

This means: skip the first row, then return 2 rows.

Result:

idusername
2bob
3carol

You will revisit these concepts in more depth when learning about pagination.

Practical examples for backend developers

To connect SELECT with typical backend tasks, imagine more realistic tables.

Example 1: Basic user listing for an admin API

Table users (extended):

idusernameemailagecreated_at
1alicealice@example.com252024-01-01 10:00:00
2bobbob@example.com312024-02-15 09:30:00
3carolcarol@example.com292024-03-05 11:45:00

Query for an admin list:

sql 
SELECT
  id,
  username,
  email,
  created_at
FROM users
ORDER BY created_at DESC
LIMIT 20;

This gives the 20 most recently created users for an admin endpoint like GET /admin/users.

Example 2: Status counts with constants

Imagine your backend needs to show the current status label together with each order.

orders:

iduser_idstatustotal_amount
11pending49.99
22shipped19.99
31shipped5.00

You might want a label that you compute in the app, but during development you can test it in SQL:

sql 
SELECT
  id,
  status,
  total_amount,
  'USD' AS currency
FROM orders;

Result:

idstatustotal_amountcurrency
1pending49.99USD
2shipped19.99USD
3shipped5.00USD

Example 3: Selecting distinct values for filters

For building filter dropdowns in a UI, you can query unique values.

sql 
SELECT DISTINCT status
FROM orders
ORDER BY status;

Your backend endpoint could use this result to populate a filter list like ["pending", "shipped", "cancelled"].

Summary

In later chapters like WHERE, ORDER BY, GROUP BY, and JOINs, you will combine these building blocks into more powerful queries. For now, practice writing simple SELECT queries on a sample database, changing the column list, adding aliases, constants, and small expressions.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3441 / 24h
Impressum / Imprint
Datenschutz / Data Policy