0

I was just writing a query when I thought of this. My query will yield exactly the same result using either inner join or left join, since I'm joining tables on a foreign key relationship, selecting first from the referenced table.

Below is a simplified example. Is there any difference (performance or otherwise) between the two queries?

I'm using postgres 9.5

Thanks

CREATE TABLE a(
    a_id INT PRIMARY KEY,
    a_name VARCHAR(10) NOT NULL
);

CREATE TABLE b(
    b_id INT PRIMARY KEY,
    a_id INT NOT NULL REFERENCES a(a_id),
    b_data VARCHAR(50) NOT NULL
);

SELECT * FROM b INNER JOIN a ON b.b_id = a.a_id;

SELECT * FROM b LEFT JOIN a ON b.b_id = a.a_id;

EDIT Please note this is not a duplicate of This question, the reason being that I'm specifically asking about the situation when joinning from referencing to a referenced table in a foreign key relationship.

Community
  • 1
  • 1
Awer Muller
  • 557
  • 7
  • 17
  • @Kritner I don't think it's a duplicate, since that question doesn't state whether the join starts from the referencing table going to referenced one like in my case. Also, the accepted answer clearly says that the results are not the same using one or the other, so I don't think the answer applies either. – Awer Muller Nov 30 '16 at 19:42
  • 2
    SQL is declarative - you tell the engine what you want and it decides what is the best plan to get it. I would argue that the duplicate may not be an exact duplicate but you can infer your answer from it. Even if the results are the same, using a LEFT JOIN may cause the engine to do more work that is not needed. At best they will be equal if the engine can know ahead of time that the extra work is not necessary. If you want to know which is faster in your case, try it both ways and measure - but that doesn't mean that one is ALWAYS faster than the other. – D Stanley Nov 30 '16 at 19:53
  • Please don't use question titles to complain about voting decisions - if you believe it is not a duplicate, put your view across in the comments. Thanks. – halfer Mar 11 '17 at 15:27

0 Answers0