SQL - Select Query

SQL - The SELECT Query

The SELECT statement is the cornerstone of SQL (Structured Query Language). It is used to retrieve, or query, data from one or more database tables. The result of a SELECT query is always a new temporary table, known as the result-set.

1. Basic Syntax of SELECT

The most fundamental SELECT statement has two main parts: SELECT to list the columns you want, and FROM to specify the table to retrieve them from.

Syntax

SELECT column1, column2, ..., columnN
FROM table_name;

Example Data

For all examples, let’s assume we have a CUSTOMERS table with the following data:

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(10, 2)
);

INSERT INTO CUSTOMERS VALUES
    (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
    (2, 'Khilan', 25, 'Delhi', 1500.00),
    (3, 'Kaushik', 23, 'Kota', 2000.00),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Hardik', 27, 'Bhopal', 8500.00),
    (6, 'Komal', 22, 'Hyderabad', 4500.00),
    (7, 'Muffy', 24, 'Indore', 10000.00);

2. Selecting Specific Columns vs. All Columns (*)

Retrieving Specific Columns

To retrieve only the ID, NAME, and SALARY for each customer, you list them explicitly:

SELECT ID, NAME, SALARY
FROM CUSTOMERS;

This query would produce the following result-set:

ID	NAME	SALARY
1	Ramesh	2000.00
2	Khilan	1500.00
3	Kaushik	2000.00
4	Chaitali	6500.00
5	Hardik	8500.00
6	Komal	4500.00
7	Muffy	10000.00

Retrieving All Columns

To fetch all columns from the table, you can use the asterisk (*) wildcard.

SELECT * FROM CUSTOMERS;

Best Practice: While SELECT * is useful for interactive exploration, you should avoid it in application code. Explicitly listing columns makes your code more readable, predictable, and resilient to schema changes. It also reduces network traffic by only fetching the data you need.

3. Creating Calculated Columns and Using Aliases

The SELECT list isn’t limited to existing columns. You can perform calculations or manipulate data and give the resulting column a new name using an alias with the AS keyword.

Example: Calculated Column

Let’s calculate a hypothetical 10% annual bonus for each customer and name the new column AnnualBonus.

SELECT
    NAME,
    SALARY,
    SALARY * 0.10 AS AnnualBonus
FROM CUSTOMERS;

Example: String Concatenation with an Alias

You can combine columns. Let’s create a single CustomerInfo column that combines the customer’s name and age.

-- Standard SQL CONCAT function
SELECT
    CONCAT(NAME, ' (Age: ', AGE, ')') AS CustomerInfo,
    ADDRESS
FROM CUSTOMERS
ORDER BY NAME;

This query produces a clean, descriptive output:

CustomerInfo	ADDRESS
Chaitali (Age: 25)	Mumbai
Hardik (Age: 27)	Bhopal
Kaushik (Age: 23)	Kota
Khilan (Age: 25)	Delhi
Komal (Age: 22)	Hyderabad
Muffy (Age: 24)	Indore
Ramesh (Age: 32)	Ahmedabad

4. Logical Order of SELECT Statement Clauses

A complete SELECT statement can have many clauses. While you write them in a specific order, the database executes them in a different, logical order. Understanding this is key to mastering SQL.

Written Order:

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...;

Logical Processing Order:

1. FROM: Gets the source table(s).
2. WHERE: Filters individual rows.
3. GROUP BY: Aggregates rows into groups.
4. HAVING: Filters the aggregated groups.
5. SELECT: Selects the final columns/expressions.
6. ORDER BY: Sorts the final result-set.
7. LIMIT / FETCH: Restricts the number of rows returned.

5. Best Practices for Writing SELECT Queries

• Format for Readability: Put each major clause (SELECT, FROM, WHERE, etc.) on a new line. Indent subqueries and join conditions. This makes complex queries much easier to debug.
• Use Meaningful Aliases: Use AS to give clear names to calculated columns or to tables in a join (e.g., FROM CUSTOMERS AS c).
• Comment Your Code: Use -- for single-line comments and /* ... */ for multi-line comments to explain the purpose of complex logic.
• Filter Early with WHERE: Apply the most restrictive filters possible in the WHERE clause. This reduces the number of rows that need to be processed by later stages like GROUP BY or ORDER BY.
• Consider Security: When building queries in an application, always use parameterized queries (prepared statements) to prevent SQL injection vulnerabilities. Never concatenate user input directly into a query string.