How to use the Subquery with JOIN In SQL
Let’s Select all rows from the sample_table where the value in the value column exceeds the average value calculated across all rows.
SELECT t1.*
FROM sample_table t1
JOIN (
SELECT AVG(value) AS avg_value
FROM sample_table
) t2 ON t1.value > t2.avg_value;
Output:
| id | name | value |
| --- | ---- | ----- |
| 4 | D | 25 |
| 5 | E | 30 |
| 6 | F | 35 |
Explanation: This is a query that will calculate the average value using a subquery and then will join it with the original table, folding rows that are greater than the average.
Show All Rows with an Above-Average Value in SQL
In SQL, finding All Rows of an Above Average Value is simple and is retrieved by the AVG() Function. There are various methods available in SQL that help us to easily find the Average value. In this guide, we will learn about various methods with detailed examples and their output.