PostgreSQL Subqueries and CTEs: Practical SQL Guide for Beginners
Subqueries and Common Table Expressions (CTEs) are important PostgreSQL concepts used to write readable queries, filter data based on other query results, and solve real-world business problems. In this tutorial, you will learn how to use subqueries, correlated subqueries, and CTEs with practical SQL examples. Sample Table We will use an employees table with the following data: employee_id employee_name department salary 1 Rahul IT 60000 2 Priya HR 40000 3 Arjun IT 75000 4 Sneha Finance 50000 5 Kiran HR 45000 6 Divya Finance 65000 What Is a Subquery? A subquery is a SQL query written inside another SQL query. It is also called an inner query or nested query. Subqueries can be used inside SELECT , FROM , WHERE , and HAVING clauses. Example 1: Find Employees Earning More Than the Average Salary SELECT employee_name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); The inner query calculates the average salary. The outer query returns employees whos...