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.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
SQL expert Rudy Limeback explains how to sort an SQL UNION query using a special ORDERY BY sequence. Continue Reading
SQL expert Rudy Limeback explains how to formulate a query using an SQL SELECT statement from a non-existing table. Continue Reading