CODEWITHSUNDEEP
mysqljoinselectforeign-keysdatabase-relations
Xtreme
Xtreme

Reputation: 1601

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

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`;

Upvotes: 1

Views: 45

Answers (1)

wally
wally

Reputation: 3592

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 :-))

Upvotes: 2

Related Questions