PostgreSQL SELECT

SELECT Components

SELECT name, email FROM users;

WHERE age > 18

ORDER BY name ASC

LIMIT 10

🔹 Basic SELECT Statement

The simplest SELECT retrieves all columns using the asterisk wildcard. You can also specify individual column names separated by commas. This is the foundation of querying data and understanding which information you need from your tables.

-- Select all columns
SELECT * FROM products;

-- Select specific columns
SELECT product_name, price FROM products;

-- Select with alias
SELECT product_name AS name, price AS cost FROM products;

name        | cost
------------+-------
Laptop      | 999.99
Mouse       | 29.99
Keyboard    | 79.99

🔹 Filtering with WHERE

The WHERE clause filters results based on conditions. You can use comparison operators like equals, greater than, or less than. Combine multiple conditions with AND or OR to create complex filters that precisely target the data you need.

-- Simple condition
SELECT * FROM products WHERE price > 50;

-- Multiple conditions
SELECT * FROM products 
WHERE price > 50 AND category = 'Electronics';

-- Using IN operator
SELECT * FROM products 
WHERE category IN ('Electronics', 'Computers');

id | product_name | price  | category
---+--------------+--------+-------------
1  | Laptop       | 999.99 | Electronics
3  | Keyboard     | 79.99  | Computers

🔹 Sorting Results

ORDER BY arranges query results in ascending or descending order. Use ASC for ascending (default) or DESC for descending. You can sort by multiple columns, and PostgreSQL will apply the order sequentially from left to right in your column list.

-- Sort ascending (default)
SELECT * FROM employees ORDER BY name;

-- Sort descending
SELECT * FROM employees ORDER BY salary DESC;

-- Sort by multiple columns
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

name        | department | salary
------------+------------+--------
Mike Brown  | IT         | 80000
John Smith  | IT         | 75000
Sarah Jones | HR         | 65000

🔹 Limiting Results

LIMIT restricts the number of rows returned by your query. This is useful for pagination or when you only need a sample of data. OFFSET skips a specified number of rows before returning results, enabling you to implement page-by-page navigation.

-- Get first 5 rows
SELECT * FROM products LIMIT 5;

-- Skip first 10, get next 5 (pagination)
SELECT * FROM products LIMIT 5 OFFSET 10;

-- Get top 3 most expensive products
SELECT * FROM products 
ORDER BY price DESC 
LIMIT 3;

product_name | price
-------------+--------
Laptop       | 999.99
Tablet       | 599.99
Monitor      | 399.99

🔹 DISTINCT Values

DISTINCT removes duplicate rows from results, returning only unique values. This is particularly useful when you want to see all different categories, departments, or other repeated values without duplicates cluttering your output and making analysis more difficult.

-- Get unique categories
SELECT DISTINCT category FROM products;

-- Get unique combinations
SELECT DISTINCT department, job_title FROM employees;

category
-------------
Electronics
Computers
Accessories

🔹 Pattern Matching with LIKE

LIKE searches for patterns in text columns. Use the percent sign as a wildcard for any number of characters, and underscore for exactly one character. This enables flexible text searches when you don't know the exact value you're looking for in the database.

-- Names starting with 'J'
SELECT * FROM employees WHERE name LIKE 'J%';

-- Names ending with 'son'
SELECT * FROM employees WHERE name LIKE '%son';

-- Names containing 'ar'
SELECT * FROM employees WHERE name LIKE '%ar%';

name        | department
------------+------------
John Smith  | IT
Sarah Jones | HR