Hacker News new | past | comments | ask | show | jobs | submit login

I can vouch for the inner joins blowing outer joins out of the water. Setting up your postgresql.conf appropriate to its environment is helpful as well, http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serve... has a good deal of explanation.



But they have quite different meaning, one can't just replace OUTER with INNER.

  Table A has m rows
  Table B has n rows
all rows return a match (aka: join on 1=1)

  OUTER JOIN will return m*n rows
  INNER JOIN will return m*n rows
none rows return a match (aka: join on 1=0)

  OUTER JOIN will return m+n rows
  INNER JOIN will return 0 zero rows
INNER join enforces that match has to exist.

OUTER join doesn't.


in my case its about the SQLAlchemy ORM which uses by default OUTER JOIN to load a set of parent objects and their related objects. If the foreign key on the parent is NOT NULL, you can replace the OUTER JOIN with INNER. So yes clearly OUTER and INNER are different but its sometimes the case that the use of OUTER is unnecessary, other times the case that a query can be restructured to not require OUTER (such as, using an EXISTS to check for "no parent rows found" instead of OUTER JOIN and a NULL check).




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: