Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL SELECT

The `SELECT` statement in PostgreSQL is used to query and retrieve data from one or more tables. This fundamental SQL command can be utilized to fetch specific columns, apply conditions, and sort the resulting data set.

Usage

The `SELECT` statement is employed whenever data needs to be extracted from a database. It is often combined with other clauses such as `WHERE`, `ORDER BY`, and `GROUP BY` to refine and organize the output.

sql
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, ...];

In this syntax, `SELECT` specifies the columns to retrieve, and `FROM table_name` indicates the source table. Other optional clauses can refine the query further.

Examples

1. Basic Select

sql
SELECT *
FROM products;

This query retrieves all columns from the `products` table, providing a complete view of each entry.

2. Selecting Specific Columns with a Condition

sql
SELECT name, price
FROM products
WHERE price > 100;

This example selects only the `name` and `price` columns from the `products` table where the price is greater than 100, filtering the results.

3. Query with Sorting

sql
SELECT name, price
FROM products
WHERE price > 100
ORDER BY price DESC;

Here, the `SELECT` statement not only filters records by price but also sorts the results in descending order of the `price` column, allowing for prioritized viewing of higher-priced items.

4. Handling NULL Values

sql
SELECT name, price
FROM products
WHERE price IS NOT NULL;

This query retrieves product names and prices while excluding any entries where the price is `NULL`.

5. Using DISTINCT to Remove Duplicates

sql
SELECT DISTINCT category
FROM products;

This query returns a list of unique categories from the `products` table, removing any duplicate entries.

6. Limiting Results with LIMIT and OFFSET

sql
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 5;

This example retrieves ten products starting from the sixth one, sorted in descending order by price.

7. Joining Tables

sql
SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;

This query retrieves order IDs and customer names by joining the `orders` and `customers` tables on their respective IDs.

Tips and Best Practices

  • Select only necessary columns. Avoid using `SELECT *` in production queries to minimize resource usage and increase performance.
  • Combine with filtering conditions. Use the `WHERE` clause to limit results to only those relevant to your needs.
  • Use aliases for clarity. Implement table and column aliases to enhance readability, especially in complex queries. For example: 
    sql
    SELECT p.name AS product_name, c.name AS category_name
    FROM products p
    JOIN categories c ON p.category_id = c.id;
  • Employ sorting and grouping wisely. Use `ORDER BY` and `GROUP BY` to organize data logically and improve data analysis.
  • Understand indexing. Be aware of indexed columns, as selecting them can significantly boost query performance.
  • Handle duplicates and NULLs carefully. Use `DISTINCT` to remove duplicates and `IS NULL`/`IS NOT NULL` to manage `NULL` values effectively.
  • Caution with complex queries. Be mindful of performance impacts, especially when dealing with large datasets or omitting indexes. Consider breaking down complex queries for better efficiency.