ALL in PostgreSQL 🚀

The ALL operator in PostgreSQL is used to compare a value to a set of values. It’s commonly used with comparison operators like =, >, <, >=, and <= in conjunction with a subquery. The result is TRUE if the comparison holds for all the values returned by the subquery.

1. Basic Syntax

SELECT column1FROM table_nameWHERE column1 operator ALL (SELECT column2 FROM table2);

• operator can be any comparison operator like =, >, <, >=, etc.
• The subquery returns a set of values, and the outer query checks if the value in column1 satisfies the comparison with all the values from the subquery.

2. Example: Get Employees with Salary Greater Than All in Another Department

SELECT name, salaryFROM employeesWHERE salary > ALL (SELECT salary FROM employees WHERE department = 'HR');

🔹 Finds employees whose salary is greater than all salaries in the HR department.

3. Example: Get Products More Expensive Than All Products in a Category

SELECT product_name, priceFROM productsWHERE price > ALL (SELECT price FROM products WHERE category = 'Electronics');

🔹 Finds products whose price is higher than any product in the Electronics category.

4. Example: Using ALL with = (Equal to All Values)

SELECT id, nameFROM studentsWHERE grade = ALL (SELECT grade FROM students WHERE course = 'Math');

🔹 Retrieves students whose grade is the same as all students in the Math course. This would return results only if all the students in the Math course have the same grade.

5. Example: Using ALL with <= (Less Than or Equal to All Values)

SELECT name, scoreFROM exam_resultsWHERE score <= ALL (SELECT score FROM exam_results WHERE subject = 'Physics');

🔹 Finds students whose score is less than or equal to all scores in the Physics exam.

6. Example: Using ALL with IN

SELECT nameFROM employeesWHERE salary IN (SELECT salary FROM employees WHERE department = 'Sales') 
AND salary > ALL (SELECT salary FROM employees WHERE department = 'HR');

🔹 Finds employees whose salary is greater than all salaries in HR and matches a salary in Sales.

7. Example: ALL with Aggregate Functions

SELECT name, salaryFROM employeesWHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department);

🔹 Finds employees whose salary is higher than the average salary in their respective departments.

8. Example: Using ALL with Subqueries in HAVING

SELECT department, AVG(salary)FROM employeesGROUP BY departmentHAVING AVG(salary) > ALL (SELECT AVG(salary) FROM employees WHERE department = 'HR');

🔹 Returns departments where the average salary is greater than the average salary in the HR department.

9. Difference Between ALL and ANY

• ALL requires that the condition must be true for all values returned by the subquery.
• ANY (or SOME) returns true if the condition is true for at least one value returned by the subquery.

Example Comparison:

Using ALL (Greater than all values)

SELECT priceFROM productsWHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

🔹 Finds products with a price higher than every product in Books.

Using ANY (Greater than any value)

SELECT priceFROM productsWHERE price > ANY (SELECT price FROM products WHERE category = 'Books');

🔹 Finds products with a price higher than at least one product in Books.

10. Performance Considerations

• Subqueries in the ALL operator can sometimes be slow with large datasets, especially if the subquery returns many rows.
• Ensure you have indexes on columns used in subqueries to improve performance.
• Use EXPLAIN ANALYZE to test query performance.

Next Steps

✅ Use ALL with subqueries for complex comparisons
✅ Optimize ALL queries for better performance
✅ Integrate ALL with Go Fiber queries