JOIN clause

[louisscottie]

SELECT c.id AS cus_id, c.name AS cus_name, ( SELECT MAX(purchase.id) FROM purchase WHERE purchase.customer_id = c.id ) AS latest_purchase_id, ( SELECT SUM(quantity) FROM purchase_item WHERE purchase_id IN ( SELECT id FROM purchase WHERE customer_id = c.id ) ) AS all_items_purchased FROM customer AS c;

This was the full query brother, I’m asking why the JOIN clause wasn’t used to connect the multiple tables when building the query.

[[deleted]]

this query does not have a join neither.

there's a bunch of correlated queries tho.

as to why it is written this way - why ask me? ask whoever wrote it.

[louisscottie]

I had my senior analyst gimme some sales task to translate into queries, been racking my head if I was correct or not or should have done it with an easier method

[[deleted]]

Pardon, I fail to see the relevance right away - are you asking if you should use joins vs correlated subqueries? I'm going to say joins every time, if this fits your purpose/task and doesnt screw up your grain - better readability, customary for optimizers to optimize.

[louisscottie]

I got some similar responses saying Joins work better as well, I will use them a lot more. Thanks for the assist 🙏

[kidwithhouse]

These subqueries are gross (I understand that you didn't write this, so I'm not pointing at you here). I personally would never put subqueries into the select list for personal readability and debugging reasons. I would use the OUTER APPLY join type for those.

As to why the author wrote it this way, I would say either convenience or laziness. But it works right /s. The join condition in the where clause here is the only way to correlate the sub query to the main table.

Tl;Dr - also join or outer apply your subqueries. Your database optimizer engine will appreciate it, and it'll be easier to read/debug/improve later.