can I rewrite a left join statement in 1 or more select statements

Question

if I have a student and their adviser I want to print every student with it's adviser even who don't have... so i have left join to student..

select student_name,nvl(adviser_name,'null')
from student left join adviser 
on student.adv_id=adviser.adv_Id

now this is a left join.. I do not always remember join as a first solution >>> i always go for select statement as a first choice so how can I change the previous sql command into single or nested select command?

Why on Earth do you want to avoid a left join? (And what do you think a "select statement" is, that it could be an alternative to one?) — ruakh, Apr 04 '15 at 05:07
I mean i can easily write a statement 'select x from y join z on y.id=z.id' as 'select x from y,z where y.id=z.id'.... can i do that with the left join too — Radi Soufan, Apr 04 '15 at 05:16
@RadiSoufan, yes you can. It's an older outer join syntax that is not standard across databases, and I would discourage its use. I find the new syntax clearer, and there are cases the old syntax can't handle. For details on the older syntax see `(+)` in http://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354 — Shannon Severance, Apr 04 '15 at 05:52

score 1 · Answer 1 · edited May 23 '17 at 11:50

tldr; Just use an OUTER join when such is the goal.

from x,y is just another way of writing a from x cross join y; and a CROSS join results in a Cartesian product - a WHERE restriction establishing a relationship between the tables makes it equivalent to a normal [INNER] JOIN with equivalent join ON condition.

.. a Cartesian product is a mathematical operation which returns a set (or product set or simply product) from multiple sets. That is, for sets A and B, the Cartesian product A × B is the set of all ordered pairs (a, b) where a ∈ A and b ∈ B.

That is, unlike an [OUTER] LEFT join, a CROSS join can't "make up the blank records" because they exist in neither A nor B.

Thus one would have to synthesize (ie. join with a derived table containing) the "missing" records before (or after, I suppose) the CROSS join was applied, which leads back to using an OUTER join to create the derived table ..

See this answer for a graphical (but non-Venn diagram) way to visualize the results; note that NULL is only introduced with the OUTER joins; not INNER or CROSS joins - or a CROSS join pretending to be an INNER join.

can I rewrite a left join statement in 1 or more select statements

1 Answers1