PostgreSQL SELECT Statement

The SELECT statement is used to query data from one or more tables in PostgreSQL. It is the most commonly used SQL statement, allowing you to filter, sort, aggregate, and join data.

1. Basic SELECT

-- Select all columns
            SELECT * FROM employees;

            -- Select specific columns
            SELECT first_name, last_name, salary FROM employees;

2. Using WHERE Clause

The WHERE clause filters rows based on a condition:

-- Employees with salary > 50000
            SELECT first_name, last_name, salary
            FROM employees
            WHERE salary > 50000;

            -- Employees in a specific department
            SELECT * FROM employees
            WHERE department_id = 10;

3. Using ORDER BY

Sort the result set by one or more columns:

-- Order by salary descending
            SELECT first_name, last_name, salary
            FROM employees
            ORDER BY salary DESC;

            -- Order by department and then salary
            SELECT * FROM employees
            ORDER BY department_id ASC, salary DESC;

4. Using LIMIT and OFFSET

Limit the number of rows returned and implement pagination:

-- Get first 5 employees
            SELECT * FROM employees
            LIMIT 5;

            -- Skip first 5 rows and get next 5
            SELECT * FROM employees
            LIMIT 5 OFFSET 5;

5. Using DISTINCT

Get unique values in a column:

-- Get unique departments
            SELECT DISTINCT department_id
            FROM employees;

6. Using Aggregate Functions

Compute summaries using COUNT, SUM, AVG, MAX, MIN:

-- Count employees
            SELECT COUNT(*) FROM employees;

            -- Average salary
            SELECT AVG(salary) FROM employees;

            -- Max and Min salary
            SELECT MAX(salary), MIN(salary) FROM employees;

7. Using GROUP BY

Group rows based on a column and apply aggregate functions:

-- Count employees per department
            SELECT department_id, COUNT(*) AS total_employees
            FROM employees
            GROUP BY department_id;

8. Using HAVING Clause

Filter groups based on aggregate results:

-- Departments with more than 5 employees
            SELECT department_id, COUNT(*) AS total_employees
            FROM employees
            GROUP BY department_id
            HAVING COUNT(*) > 5;

9. Using Joins in SELECT

Retrieve related data from multiple tables:

-- Get employee names with department names
            SELECT e.first_name, e.last_name, d.department_name
            FROM employees e
            INNER JOIN departments d
            ON e.department_id = d.id;

            -- Left join example
            SELECT e.first_name, e.last_name, d.department_name
            FROM employees e
            LEFT JOIN departments d
            ON e.department_id = d.id;

10. Using Aliases

Provide temporary names to columns or tables:

-- Column alias
            SELECT first_name AS "First Name", last_name AS "Last Name"
            FROM employees;

            -- Table alias
            SELECT e.first_name, e.last_name, d.department_name
            FROM employees AS e
            JOIN departments AS d
            ON e.department_id = d.id;

11. Subqueries in SELECT

Use queries inside queries to fetch dynamic results:

-- Employees earning more than average salary
            SELECT first_name, last_name, salary
            FROM employees
            WHERE salary > (SELECT AVG(salary) FROM employees);

12. Tips for Efficient SELECT Queries

• Use proper indexes for frequently queried columns.
• Avoid SELECT * in production queries; select only necessary columns.
• Use LIMIT for large tables to reduce data retrieval overhead.
• Use EXPLAIN ANALYZE to understand query execution plans.
• Consider partitioning or materialized views for very large datasets.

Conclusion

The SELECT statement is versatile and powerful in PostgreSQL. Mastering its features, including filtering, ordering, aggregation, joins, and subqueries, is essential for efficient database querying and reporting.