To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
We've been getting a number of this type of question lately: short,
and simple on the surface. Short questions are wonderful, because they
leave more room for the answer. Simple questions are even better,
as long as they aren't too simple.
In this case, the question is not too simple, but I have difficulty
imagining the circumstances which would lead to this requirement.
Here's the LEFT OUTER JOIN way to do it:
select A.foo
, A.bar
, B.qux
from table1 as A
left outer
join table2 as B
on B.Aid = A.id
As you know, a LEFT OUTER JOIN returns all rows from the left
table, with matching rows from the right table, if any. This is
the same as an INNER JOIN combined in a UNION with a query on the
left table only, with a NOT EXISTS subquery:
select A.foo
, A.bar
, B.qux
from table1 as A
inner
join table2 as B
on B.Aid = A.id
union all
select A.foo
, A.bar
, null
from table1 as A
where not exists
( select *
from table2
where Aid = A.id )
As mentioned, it's pretty hard to imagine why you wouldn't want to use
the LEFT OUTER JOIN.
|
