OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Without joins

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Without joins [message #8228]

Tue, 05 August 2003 04:08

capri
Messages: 4
Registered: July 2003

Junior Member

select a.ename,a.sal,a.deptno,b.loc from emp a,dept b
where a.deptno=b.deptno(+);
I have to write a query without using joins how to go abt this thanks is advance

Report message to a moderator

Re: Without joins [message #8233 is a reply to message #8228]

Tue, 05 August 2003 06:57

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

_{quote from capri:}
----------------------------------------------------------------------

select a.ename,a.sal,a.deptno,b.loc from emp a,dept b
where a.deptno=b.deptno(+);
I have to write a query without using joins how to go abt this thanks is advance

----------------------------------------------------------------------
Capri, with all due respect, that is like saying, "I have to drive to work without using wheels how to go abt this thanks in advance".

Joins are not evil! [[T]]he database was built to join, joins are not evil, improperly designed tables (eg: a lookup without an index perhaps) are. Joins are part and parcel of how databases work. If you work with databases but don't use joins, you won't be working with databases for very long.

Just out of curiosity, what is the source of your "requirement" to avoid joins? This class of request ("I want to go swimming, but without getting myself wet") never ceases to puzzle me.

A.

Report message to a moderator

Re: Without joins [message #8236 is a reply to message #8233]

Tue, 05 August 2003 08:50

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

Maybe this is a homework assignment where they want you to research natural joins. Now those are evil.

btw
> where a.deptno=b.deptno(+);
Is it just me or is that back to front? I know SQL doesn't care, it makes no difference to the logic, and SQL textbooks tend to write outer joins this way around, but ask yourself would you write

WHERE 'Smith' = ename

or

WHERE ename = 'Smith'

Outer joins are confusing enough without everybody writing them backwards. Stop the madness, write

WHERE b.deptno (+)= a.deptno;

Report message to a moderator