2 ms·
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 o
by adamzochowski 16y ago
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.
- zzzeek 16y agoin 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).