PostgreSQL SELECT

PostgreSQL SELECT Statement

The SELECT statement in PostgreSQL is used to retrieve data from a table. You can filter, sort, group, and modify the data.

1. Basic SELECT Syntax

SELECT column1, column2, ...
FROM table_name;

• Retrieves specific columns from a table.
• Use * to select all columns.

Example: Select All Columns

SELECT * FROM employees;

🔹 Fetches all rows and columns from the employees table.

Example: Select Specific Columns

SELECT id, name, salary FROM employees;

🔹 Only fetches id, name, and salary columns.

2. Using WHERE to Filter Data

The WHERE clause filters records based on a condition.

SELECT * FROM employees
WHERE department = 'IT';

🔹 Fetches only employees in the IT department.

Common Operators for Filtering

Operator	Description	Example
=	Equals	WHERE salary = 50000
!= or <>	Not equal	WHERE department <> 'HR'
>	Greater than	WHERE salary > 50000
<	Less than	WHERE age < 30
>=	Greater than or equal	WHERE salary >= 40000
<=	Less than or equal	WHERE age <= 50
BETWEEN	Between two values (inclusive)	WHERE salary BETWEEN 40000 AND 60000
IN	Matches any value in a list	WHERE department IN ('IT', 'HR', 'Sales')
LIKE	Pattern matching (wildcards % and _)	WHERE name LIKE 'J%' (names starting with J)
IS NULL	Checks for NULL values	WHERE email IS NULL
IS NOT NULL	Checks for non-NULL values	WHERE phone IS NOT NULL

3. Sorting Results (ORDER BY)

To sort query results, use ORDER BY:

SELECT name, salary FROM employees
ORDER BY salary DESC;

🔹 Sorts employees by salary from highest to lowest.

Sorting Multiple Columns

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

🔹 Sorts by department (A-Z), then salary (highest to lowest).

4. Limiting Rows (LIMIT and OFFSET)

Get the Top 5 Highest-Paid Employees

SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 5;

Pagination: Skip the First 10 Rows

SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 10;

🔹 Gets the next 5 highest-paid employees after skipping the top 10.

5. Using DISTINCT to Remove Duplicates

SELECT DISTINCT department FROM employees;

🔹 Returns unique department names.

6. Using CASE for Conditional Logic

SELECT name, salary,
  CASE 
    WHEN salary > 60000 THEN 'High'
    WHEN salary BETWEEN 40000 AND 60000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_category
FROM employees;

🔹 Categorizes salaries into "High", "Medium", and "Low".

7. Using JOIN to Fetch Data from Multiple Tables

Inner Join

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

🔹 Fetches employee names along with their department names.

Left Join (Include Unmatched Rows from Left Table)

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

8. Aggregating Data (COUNT, SUM, AVG, MAX, MIN)

Count Employees in Each Department

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Calculate Total Salary

SELECT SUM(salary) FROM employees;

Find the Highest Salary

SELECT MAX(salary) FROM employees;

9. Grouping Data (GROUP BY & HAVING)

Find Departments with More than 5 Employees

SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

10. Subqueries (SELECT Inside SELECT)

Find Employees with the Highest Salary

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

🔹 The subquery finds the highest salary, and the outer query fetches the employee(s) with that salary.

Summary

Feature	Example
Select All Columns	SELECT * FROM table_name;
Select Specific Columns	SELECT column1, column2 FROM table_name;
Filtering (WHERE)	SELECT * FROM table WHERE salary > 50000;
Sorting (ORDER BY)	SELECT * FROM table ORDER BY column DESC;
Limit Rows (LIMIT)	SELECT * FROM table LIMIT 5;
Remove Duplicates (DISTINCT)	SELECT DISTINCT column FROM table;
Conditional Logic (CASE)	SELECT name, CASE WHEN age > 50 THEN 'Senior' ELSE 'Junior' END FROM table;
Join Tables (JOIN)	SELECT a.name, b.department FROM employees a INNER JOIN departments b ON a.department_id = b.id;
Aggregates (COUNT, SUM, etc.)	SELECT COUNT(*) FROM table;
Grouping (GROUP BY)	SELECT department, COUNT(*) FROM employees GROUP BY department;
Subqueries (SELECT inside SELECT)	SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

Would you like a specific example based on your project? 🚀