# Need further clarification on correlated subqueries

**URL:** <https://forum.codewithmosh.com/t/need-further-clarification-on-correlated-subqueries/2303>\
**Category:** SQL\
**Created:** [January 27, 2021, 10:35pm UTC](https://forum.codewithmosh.com/t/need-further-clarification-on-correlated-subqueries/2303 "2021-01-27T22:35:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![pfabbi](https://avatars.discourse-cdn.com/v4/letter/p/57b2e6/32.png) [@pfabbi](https://forum.codewithmosh.com/u/pfabbi)\
**Post date:** [January 27, 2021, 10:35pm UTC](https://forum.codewithmosh.com/t/need-further-clarification-on-correlated-subqueries/2303/1 "2021-01-27T22:35:15Z")

</div>

I know how to create correlated subqueries, but I don’t fully understand them… If you look at my first demo, I’m not using the “i” alias and in the second one I am using it. If you look closely, I’m have different results from both. Why is that? Because it’s coming from the same table, that being “invoices”, and either way the numbers in each demo are all above average. Is it for more accurate results? Or something else?

 ![client_id](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/1X/f44a2cc5e8e66c24e64c82b5651e2ea5546a4383.png)

 ![i_client_id](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/1X/01de8d312804e5f40e57fd458411c7bc1174e20c.png)

---

<div class="post-metadata">

**Author:** ![SAM](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/sam/32/167_2.png) [@SAM](https://forum.codewithmosh.com/u/SAM)\
**Post date:** [January 27, 2021, 11:19pm UTC](https://forum.codewithmosh.com/t/need-further-clarification-on-correlated-subqueries/2303/2 "2021-01-27T23:19:10Z")

</div>

Your first query is not correlated at all. With

```sql
SELECT AVG(invoice_total)
FROM INVOICES
WHERE client_id = client_id

```

you are selecting the average invoice total from ALL invoices in the table since your `WHERE` clause is true for every single row in that table. You could have also written `WHERE 1 = 1` or omitted the `WHERE`-clause altogether.

Correlating means referring to a value from the outer query in the inner query. That’s what `WHERE client_id = i.client_id` in the second example does. `i.client_id` is the client\_id currently investigated by the outer query.

So the first query returns invoices with a total higher than the average total of all invoices in the system whereas the second query returns invoices with a total higher than the average total _that specific customer_ usually has. That has noting to do with accuracy - it’s a totally different query.

---

<div class="post-metadata">

**Author:** ![pfabbi](https://avatars.discourse-cdn.com/v4/letter/p/57b2e6/32.png) [@pfabbi](https://forum.codewithmosh.com/u/pfabbi)\
**Post date:** [January 28, 2021, 4:36pm UTC](https://forum.codewithmosh.com/t/need-further-clarification-on-correlated-subqueries/2303/3 "2021-01-28T16:36:25Z")

</div>

Okay thanks @SAM, I’ll keep that in mind.

---

<div class="post-metadata">

**Author:** ![ljoy](https://avatars.discourse-cdn.com/v4/letter/l/c6cbf5/32.png) [@ljoy](https://forum.codewithmosh.com/u/ljoy)\
**Post date:** [January 28, 2021, 5:51pm UTC](https://forum.codewithmosh.com/t/need-further-clarification-on-correlated-subqueries/2303/4 "2021-01-28T17:51:44Z")

</div>

Thank you, Your description was very helpful. (I’m just beginning to wrap my mind around SQL.)

@pfabbi, I am glad you asked that question, so thanks to you, too!