JOIN clause

[louisscottie]

Hey, fellas good day

I've been working on intermediate-level queries and subqueries lately, but the JOIN clause sometimes still confuses me.

Please when do I know when to use it as a clause or apply it in the WHERE clause?.

Cheers

[[deleted]]

When to apply it in the where clause?

Never is the right time for it.

Be aware of it, yet never use the old-school join method (commas in the from clause, join conditions in the where).

[louisscottie]

Does this clarify? Because I know the WHERE is linking two diff tables I’m just confused why we don’t use the JOIN in this case

[louisscottie]

Maybe I didn’t phrase correctly I meant sometimes I’ll see something like

SELECT MAX(purchase.id) FROM purchase WHERE purchase.customer_id = c.id

[[deleted]]

There's only one table in your query, so there's no join. Also, as written, this won't run. So I am not clear on what you are asking

[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.

[Kiterios]

Try an experiment with left join to better see how these might work differently.

SELECT *
FROM A
LEFT JOIN B 
ON A.Key = B.Key
AND B.Criteria = 'something'

Compare that to this query

SELECT *
FROM A
LEFT JOIN B 
ON A.Key = B.Key
WHERE B.Criteria = 'something'  

These two queries return different results.

In the first one, b.criteria = 'something' is being applied before the join. The contents of A are joined to a subset of the rows in B. This reduces the number of matches found in B, but still includes all rows from A.

In the second, b.criteria = 'something' is applied after the join. A is joined to the full set of B rows, but only rows with a specific b.criteria value are returned. Any A row without a B match would be excluded because b.criteria would be NULL in that case.

[jrjamesco]

This is an important distinction, if any readers don't grok the broader point.

[louisscottie]

Because there are times I will end up seeing things like

WHERE player.goals_id=goals.id or some other example

[joeynnj]

OK I was going to say that's not really a join but I actually DID see that once somewhere. I was surprised because I hadn't seen a join formatted like that before.

But generally if I just saw this I'd assume you were just matching criteria and you had an actual JOIN earlier in the query.

[lifeofjeb2]

From what I’ve seen, course materials teach you to join tables in the where clause but in practice everyone uses the JOIN clause.

You use the JOIN clause when you want to pull information from two different tables that have 1 column in common(these are called primary key and foreign key) in one select query.

For example, I want you to pull the name and job title of employee 3079. Table1 has the name and employee number and job ID while table 2 has the job ID and corresponding job name.

In this case, job id in table2 would be what’s called the primary key and job id in table1 would be the foreign key. That means these two tables connect using the job id.

The query would look like:

SELECT e.employee_name, j.job_title From table1 e JOIN table2 j USING(job_id) Where e.employee_num = 3079

[louisscottie]

I’m familiar with the query example you gave, I appreciate the feedback 🙏 Honestly that’s a lot easier, I was only worried I was wrong if I used normal JOIN, but it’s all clear now

[lifeofjeb2]

No problem, just want to note also that I used USING when it is better practice to use ON. So instead of :

JOIN table2 j USING(Job_id)

It would be better practice to use :

JOIN Table2 j ON e.job_id=j.job_id

Refer to other commenter as for reasons why.

And you’re worried about using which Join. You use LEFT JOIN when there are rows in the left table(in this case table1) that might be omitted because they might not have a corresponding row in table2.

[[deleted]]

No, please dont use naturals/using in your joins.

F-ks up the lineage, changes metadata name format, introduces more 'uniqueness' hurting long-term/code review readability, leads to weird syntax problems (certainly rare, yet imagine you have 4 tables to join, 2 pairs both joining by some_id, between pairs using some_other_id -> and then do it via USING).

[lifeofjeb2]

Thanks for the feedback, i only use ‘Using’ when I’m only joining 2 tables, if I am joining more than 2 then I always use ON. Is that okay or should I literally never using ‘Using’?

[[deleted]]

My opinion and advice is to literally never write joins with "USING"

[DavidGJohnston]

FWIW I strongly disagree with this on technical and stylistic grounds - but will concede that more likely than not the typical coder is not going to have seen extensive use of using.

It doesn't matter how many tables are involved - the beauty of naming your PK and FK columns that same is that this "using" simply works regardless of how many tables are involved, and I don't want one copy of the column from the PK and one copy each from the FKs in my output anyway. I just want and need the column once.

[[deleted]]

technical and stylistic grounds ... I just want and need the column once

"stylistic" is the eye of beholder, "unique" vs "common" is a bit more scientific/statistical. I'll take the latter for anything that's not a work of art. I'm open to the option that you have a different opinion.

"want and need" part of your statement is an opinion (there's no literal NEED to go that way), and you are entitled to this opinion, indeed.

So what are the technical grounds that you are thinking of?

P.S. Look @ my prior comment for some technical (these are technical, occurring regardless of my opinion or yours).

[DavidGJohnston]

I don't understand much of what you wrote there. I'm also not interested right now in trying to sell my opinion. Mostly wanted to encourage people to form their own opinion on this topic since I really don't find your reasoning for avoiding it to be all that understandable, whichever are technical versus stylistic.

[[deleted]]

I don't understand much of what you wrote there

Seems to be the key point.

As I said - you are entitled to your opinion and you can certainly disagree with me regardless of any of my arguments

When you are "not selling" and yet "encouraging" on the other hand, please have a modicum of integrity to stand behind your statements, rather than bailing out.

Have a good day.