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?
Your first query is not correlated at all. With
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.
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!
1 Like