The cost of left out join versus a select I'm currently working with Ebean for database abstraction. My test database is Postgresql 9.3

I turned on logging for the SQL queries to test some code behavior.

So, assume 2 tables, OneToMany relation. In the code, this will have fields on the objects to represent it.

So for instance, class A will have a list of class B instances as a field.

Suppose I want to get those instances of class B, that are related to class A.

I could simply do return aInstance.bInstances

Or, I could write an Ebean function that asks for the ID of A and gets all the instances from the B table where A is present. The result is the same.

I did both, to see what SQL query Ebean would generate. And it does generate a different one.

The first, asking for the instances related to the object, will get Ebean to generate a statement with a left outer join based on the ID of the instance of A.

The second will do a select on the B table.

I'm wondering about the performance of this. On my test setup, which is fairly small, the left outer join is always faster. But I wonder about production databases that are constantly being updated. I did some googling in to the performance of left outer join and it's pretty much all bad things I've found.

So, what's the hidden cost here? Will left outer join works fast at first but mess things up later? Will the simple select statement prove more reliable, if a bit slower?
If you already have the PK value of a certain row in A, a simple SELECT is the fastest possible way:

SELECT * FROM b WHERE a_id = ?


If you need to retrieve a_id first, a join is your best options. Make that a LEFT [OUTER] JOIN only if there can be cases where there are no matching rows in B and you still want a_id in that case (or whatever, you didn't clarify).

SELECT a.a_id, b.*  -- often you don't need all columns
FROM   a
LEFT   JOIN b USING (a_id)
WHERE  a.some_column = ?;  -- not defined


One integrated query is typically faster than two separate queries. LEFT [OUTER] JOIN or [INNER] JOIN perform about the same here.