Source: zaidesanton newsletter

Understanding Subqueries in SQL/PostgreSQL

Subqueries, also known as inner queries or nested queries, are a powerful feature in SQL that allows you to perform complex queries by nesting one query within another. They can be used to retrieve data that will be used in the main query, enabling you to execute more sophisticated data operations.

What is actually subquery ?

Let me clear it. A subquery is a query embedded within another SQL query. It can be used in various clauses, such as SELECT, FROM, WHERE, and HAVING. Subqueries can return a single value, a single column, or multiple columns and rows, depending on the context in which they are used.

Types of Subqueries

• Single - Value Subquery
• Multiple-row Subquery

Basic example

1SELECT column1, column2, ...
2FROM table_name
3WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);
4

1. Single-Value Subquery

Let's start with a simple example. Suppose we have a students table and we want to find the name of the student with the highest score.

1SELECT name
2FROM students
3WHERE score = (SELECT MAX(score) FROM students);
4

2.Multiple-row Subquery

This query return multiple rows within single column.

Here we want to know the employees detail who earn more than average salary of each department.

1SELECT name, salary
2FROM employees e
3WHERE salary > (
4    SELECT AVG(salary)
5    FROM employees
6    WHERE department_id = e.department_id
7);
8

Best Practices for Using Subqueries

• Performance: Sometimes, subqueries can lead to performance issues. In such cases, consider using JOIN operations as an alternative.
• Readability: While subqueries can make queries more readable by organizing logic, excessive nesting can lead to complex and hard-to-read SQL code. Aim for clarity.
• Limit Rows: Use LIMIT or DISTINCT within subqueries when necessary to avoid unintentional duplicates.
  
  To be continue......