SELECT in PostgreSQL

The SELECT statement in PostgreSQL is used to query and retrieve data from one or more tables. It can include various clauses like WHERE, JOIN, ORDER BY, and more to refine the data returned.

1. Basic SELECT Query

SELECT * FROM employees;

✅ Retrieves all columns from the employees table.

2. Selecting Specific Columns

SELECT first_name, last_name FROM employees;

✅ Retrieves only the first name and last name columns from the employees table.

3. Using WHERE to Filter Rows

SELECT * FROM employees WHERE department = 'IT';

✅ Retrieves employees who work in the IT department.

4. Sorting Results with ORDER BY

SELECT * FROM employees ORDER BY salary DESC;

✅ Retrieves all employees sorted by salary in descending order.

5. Using LIMIT to Restrict Results

SELECT * FROM employees LIMIT 5;

✅ Retrieves the first 5 rows from the employees table.

6. Using DISTINCT to Remove Duplicates

SELECT DISTINCT department FROM employees;

✅ Returns unique departments without duplicates.

7. Using JOIN to Combine Tables

SELECT e.first_name, e.last_name, d.department_name FROM employees eJOIN departments d ON e.department_id = d.id;

✅ Combines the employees and departments tables to show the first name, last name, and department name of each employee.

8. Using Aggregation Functions (SUM, AVG, COUNT)

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

✅ Retrieves the average salary for each department.

9. Using GROUP BY for Grouped Results

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

✅ Retrieves the number of employees in each department.

10. Combining WHERE and LIKE for Pattern Matching

SELECT * FROM employees WHERE first_name LIKE 'J%';

✅ Retrieves employees whose first name starts with 'J'.

11. Using IN for Multiple Conditions

SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');

✅ Retrieves employees from the IT, HR, or Finance departments.

12. Using Subqueries in SELECT

SELECT first_name, last_name FROM employees WHERE department_id = (SELECT id FROM departments WHERE department_name = 'IT');

✅ Retrieves employees from the IT department using a subquery to find the department ID.

13. Using EXISTS to Check for Data

SELECT * FROM employees eWHERE EXISTS (    SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.location = 'New York');

✅ Retrieves employees whose department is located in New York.

14. Selecting Data with CASE (Conditional Logic)

SELECT first_name, last_name,     CASE         WHEN salary > 60000 THEN 'High'        ELSE 'Low'    END AS salary_categoryFROM employees;

✅ Adds a column salary_category based on the salary value ('High' if salary > 60,000, 'Low' otherwise).

Performance Tip

• For large datasets, indexes on frequently queried columns can improve query performance.
• Always consider limit or pagination when working with large result sets to prevent performance bottlenecks.