PostgreSQL - SELECT

SELECT statement is the primary command used to query data in PostgreSQL. It enables users to retrieve records from one or more tables, apply filtering conditions, sort and group results, limit returned rows, and generate summary information using aggregate functions. As the foundation of data retrieval in PostgreSQL, it plays a central role in database querying and analysis.

Example:

SELECT * FROM employees;

Syntax:

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column_name]
[ORDER BY column_name]
[LIMIT number];

Examples

Prerequisites: Assume the following table is available:

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
age INT,
department VARCHAR(50),
salary NUMERIC
);

Example 1: Selecting All Columns

To retrieve all columns from the employees table, you can use the * wildcard:

SELECT * FROM employees;

Output:

Explanation:

• * selects all columns from the table.
• Every row in the employees table is returned.
• Useful for exploring data, but selecting only required columns is generally recommended.

Example 2: Selecting Specific Columns

If we only want to retrieve specific columns, you can list them explicitly:

SELECT name, salary FROM employees;

Output:

Example 3: Using the WHERE Clause

To filter results based on specific conditions, we can use the WHERE clause. For example, to retrieve employees in the IT department:

SELECT * FROM employees WHERE department = 'IT';

Output:

Example 4: Using the ORDER BY Clause

We can sort the results using the ORDER BY clause. For example, to retrieve employees sorted by salary in descending order:

SELECT * FROM employees ORDER BY salary DESC;

Output:

Example 5: Using the LIMIT Clause

To limit the number of rows returned, use the LIMIT clause. For instance, to retrieve only the top 3 highest-paid employees:

SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

Output:

Example 6: Using GROUP BY

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. For example, to find the average salary by department:

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Output:

Example 7: Using DISTINCT

The DISTINCT keyword removes duplicate values from the result set and returns only unique records for the specified column(s).

SELECT DISTINCT department
FROM employees;

Output:

Example 8: Using Column Aliases

Column aliases rename columns in the query output, making the results more readable and easier to understand.

SELECT
name AS employee_name,
salary AS annual_salary
FROM employees;

Output:

Example 9: Using AND

The AND operator combines multiple conditions in a WHERE clause and returns only the rows that satisfy all specified conditions.

SELECT *
FROM employees
WHERE department = 'IT'
AND salary > 80000;

Output:

Note: The performance of a SELECT query depends on factors such as table size, indexes, filtering conditions, and sorting operations. Proper indexing can significantly improve query execution time.

Improving SELECT Query Performance

• Avoid using SELECT * in production queries.
• Retrieve only the columns required.
• Use aliases to improve readability.
• Filter unnecessary rows using WHERE.
• Use LIMIT when working with large datasets.
• Add indexes to frequently filtered columns.