Why should I use JOIN if I only need info from the table with foreign key?

Question

I have a table Customer with Primary key customerNumber. I also have a table table customerOrder that has a foreign key FK_customerNumber to customerNumber in the Customer table.

I know the customerNumber and need to select only order information related to that user.

I see a lot of tutorials using a JOIN like this

SELECT 
projectNumber, orderNumber
FROM
    `customerOrder` t1
        INNER JOIN `customer` t2 
            ON t1.`FK_customerNumber` = t2.`customerNumber`
        WHERE 
            t2.`customerNumber` = 50;

It's working but why can't I just select the FK? I get the same result. The value of the FK in customerOrder and PK in customer are the same.

This gives me the same result without JOIN

SELECT 
projectNumber, orderNumber
FROM
    `customerOrder`
WHERE 
    `FK_customerNumber` = 50;

I understand if I need info from both tables like this but now I only need info from the customerOrder

SELECT customerOrder.`projectNumber`, customer.`username`
FROM `customerOrder`
INNER JOIN customer ON customerOrder.`FK_customerNumber` = customer.`customerNumber`;

if you only need the value for the fk and projectNumber, orderNumber are in table projectNumber, orderNumber then you don't need the join — ScaisEdge, Feb 12 '21 at 10:30
I doubt "I see a lot of tutorials using a JOIN like this". Tutorials with left join maybe, which is different than this post. What is an example? Anyway what's the point of your post? What is your question? There's no question here. — philipxy, Feb 12 '21 at 10:46
@philipxy The question is "why can't I just select the FK?". I can (get the same result) but is it bad to do this? The reason for my question is that many tutorials do not make a select directly against a FK and is there a reason for that? — Xtreme, Feb 12 '21 at 10:57
I think this is a very valid question. It brings into question "readability", "good practice", and "optimisations". — wally, Feb 12 '21 at 11:00
Please put what's needed to ask in your post body, not just the title. Again, I don't believe "I see a lot of tutorials using a JOIN like this", quote some. Yes, you give an example of a needlessly complicated query, but there's no reason to think it's not needlessly complex because there aren't a lot of examples of it, because it's needlessly complex. PS Please give DDL. Don't describe code when you can just give it. [mre] — philipxy, Feb 12 '21 at 12:04

wally · Answer 1 · 2021-02-12T11:05:56.190

Why can't I just select the FK

You can.

I can't speak for the tutorial writers, but I'd hazard a guess that they're trying to demonstrate how the JOIN works - the principle that you've something on the left and something related on the right, and a JOIN is how you associate those two things.

If you perform an EXPLAIN SELECT ... - you'll likely see that MySQL has optimised out looking at the left table altogether, as it knows you're only really dealing with data on the right.

The only scenario I could foresee necessitating the JOIN is if there's no foreign key constraint on the right. In that scenario, the more verbose SELECT ... FROM ... JOIN ... would ensure that you don't get anything from the right if it's missing from the left (which the foreign key constraint would prevent from being the case).

Other reasons you might opt to be more verbose:

The query is "more explicit" - someone coming to it afresh can see the relationship of the two tables at a glance (that might be desirable, depending on context)
You intend to pull some additional data from the left at some point soon (feature isn't completed in code yet)

Equally you might explicitly choose not to include the join for the sake of ensuring optimal performance. (But as I say, you'd be surprised at how good MySQL is at optimising on its own :-))

Why should I use JOIN if I only need info from the table with foreign key?

1 Answers1