Home » SQL & PL/SQL » SQL & PL/SQL » Query regarding Outer Join (Oracle 11g , Windows 7)
Query regarding Outer Join [message #635248] Wed, 25 March 2015 09:31 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Hi

It is said that For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.

But in my below three SQL are behaving like left outer join .Among this 3 SQL actually which is considered as left join non-ANSI SQL query

select d.user_id, DD.DEPT_DESC  from user_details d, dept_master dd where dd.DEPT_ID(+) = D.DEPT_ID ; 
 
select d.user_id, DD.DEPT_DESC  from dept_master dd, user_details d where dd.DEPT_ID(+) = D.DEPT_ID ; 

select d.user_id, DD.DEPT_DESC  from user_details d, dept_master dd where d.DEPT_ID = Dd.DEPT_ID(+)


Table & Insert script

CREATE TABLE DEPT_MASTER 
(DEPT_ID NUMBER(2) Primary key,
DEPT_DESC VARCHAR2(100 BYTE) NOT NULL,
S_DESCRIPTION VARCHAR2(100 BYTE),
N_STATUS NUMBER(1) DEFAULT 0 NOT NULL,
D_SYSDATE DATE DEFAULT sysdate);

CREATE TABLE USER_DETAILS
( USER_ID NUMBER(10) Primary key,
FIRST_NAME VARCHAR2(50 BYTE) NOT NULL,
LAST_NAME VARCHAR2(50 BYTE) NOT NULL,
DEPT_ID NUMBER(2),
ACTIVE_STATUS NUMBER(1) NOT NULL,
GROUP_ID NUMBER(2) NOT NULL);

 insert into DEPT_MASTER (DEPT_ID, DEPT_DESC, S_DESCRIPTION, N_STATUS)
values (1, 'ww', 'ww', 1);
 
insert into USER_DETAILS (USER_ID, FIRST_NAME, LAST_NAME, DEPT_ID, ACTIVE_STATUS, GROUP_ID)
values (1, 'a', 'b', 1, 1, 1);

insert into USER_DETAILS (USER_ID, FIRST_NAME, LAST_NAME, DEPT_ID, ACTIVE_STATUS, GROUP_ID)
values (2, 'c', 'd', null, 1, 1);

commit ;

[Updated on: Wed, 25 March 2015 09:34]

Report message to a moderator

Re: Query regarding Outer Join [message #635250 is a reply to message #635248] Wed, 25 March 2015 09:45 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
rakeshramm wrote on Wed, 25 March 2015 14:31
Hi

It is said that For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.


Only in an outer join. I don't understand your question (actually I couldn't really find it).
Re: Query regarding Outer Join [message #635253 is a reply to message #635248] Wed, 25 March 2015 09:58 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Hi

It is said if we use left outer join for all rows in table A that have no matching rows in table B, Oracle Database returns all records from A ie :(In left outer join tables A and B and returns all rows from table A even though there is no records in table B)

But in my below three SQL are behaving like left outer join . Among this 3 SQL actually which is considered as left join non-ANSI SQL query


select d.user_id, DD.DEPT_DESC  from user_details d, dept_master dd where dd.DEPT_ID(+) = D.DEPT_ID ; 
 
select d.user_id, DD.DEPT_DESC  from dept_master dd, user_details d where dd.DEPT_ID(+) = D.DEPT_ID ; 

select d.user_id, DD.DEPT_DESC  from user_details d, dept_master dd where d.DEPT_ID = Dd.DEPT_ID(+)


Table & Insert script

CREATE TABLE DEPT_MASTER 
(DEPT_ID NUMBER(2) Primary key,
DEPT_DESC VARCHAR2(100 BYTE) NOT NULL,
S_DESCRIPTION VARCHAR2(100 BYTE),
N_STATUS NUMBER(1) DEFAULT 0 NOT NULL,
D_SYSDATE DATE DEFAULT sysdate);

CREATE TABLE USER_DETAILS
( USER_ID NUMBER(10) Primary key,
FIRST_NAME VARCHAR2(50 BYTE) NOT NULL,
LAST_NAME VARCHAR2(50 BYTE) NOT NULL,
DEPT_ID NUMBER(2),
ACTIVE_STATUS NUMBER(1) NOT NULL,
GROUP_ID NUMBER(2) NOT NULL);

 insert into DEPT_MASTER (DEPT_ID, DEPT_DESC, S_DESCRIPTION, N_STATUS)
values (1, 'ww', 'ww', 1);
 
insert into USER_DETAILS (USER_ID, FIRST_NAME, LAST_NAME, DEPT_ID, ACTIVE_STATUS, GROUP_ID)
values (1, 'a', 'b', 1, 1, 1);

insert into USER_DETAILS (USER_ID, FIRST_NAME, LAST_NAME, DEPT_ID, ACTIVE_STATUS, GROUP_ID)
values (2, 'c', 'd', null, 1, 1);

commit ;

Re: Query regarding Outer Join [message #635254 is a reply to message #635248] Wed, 25 March 2015 10:01 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
I am afraid with repeating the same content you will tend to get the same answers, anyway

It is said where? Oracle documentation? If so, they have table aliased A and B appropriately - A is before B in the FROM clause.

Not sure what is your definition of "behaving like left outer join"; if you mean replacement of comma with LEFT JOIN keyword and WHERE clause with ON clause and removal of Oracle outer join operator (+), it is not - note that the second query will differ from the other ones.

Is this (swapping tables in FROM clauses) your "problem"? What is the question?
Re: Query regarding Outer Join [message #635255 is a reply to message #635253] Wed, 25 March 2015 10:02 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
It sounds like you're getting hung up on table A and B. They are relative to the position of the (+) and not absolute (also this is a LOT easier to see and understand if you use ANSI joins, try rewriting them that way to help your understanding).

However...if I'm wrong then you'll need to post what you expect from the queries above.
Re: Query regarding Outer Join [message #635256 is a reply to message #635255] Wed, 25 March 2015 10:16 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
This:
select d.user_id, DD.DEPT_DESC  from user_details d, dept_master dd where dd.DEPT_ID(+) = D.DEPT_ID ;

is equivalent to both of these:
SELECT d.user_id, DD.DEPT_DESC  
from user_details d LEFT JOIN dept_master dd ON dd.DEPT_ID = D.DEPT_ID;

SELECT d.user_id, DD.DEPT_DESC
FROM dept_master dd RIGHT JOIN user_details d ON dd.DEPT_ID = D.DEPT_ID;


Your other two examples are also both equivalent to either of the above ANSI versions.

(+) goes against the columns from the optional table.
LEFT indicates the mandatory table is to the left of the join keyword.
RIGHT indicates the mandatory table is to the right of the join keyword.

(+) verses ANSI is two completely different ways of indicating which table is optional and which is mandatory.
Re: Query regarding Outer Join [message #635354 is a reply to message #635256] Fri, 27 March 2015 09:17 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


Thank you cookiemonster

Thanks all for the response


Re: Query regarding Outer Join [message #635368 is a reply to message #635354] Sat, 28 March 2015 06:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
And just because I want to but in, not everyone agrees that ANSI SYNTAX is better, or easier to understand. I don't like it. Then again I am stupid in many ways. Just saying.

Kevin
Re: Query regarding Outer Join [message #635376 is a reply to message #635368] Sat, 28 March 2015 09:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
"Which one is a better syntax" is debatable. For example, the confusion is more when it comes to FULL OUTER JOIN. Newbies get surprised when they put the + on both sides expecting the query to perform a full outer join, however they end up with an error.

Oracle recommendation herehttp://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm#sthref3175:

Quote:
Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax


So, in my opinion, it depends. I use the Oracle syntax comfortably unless I am aware I need to use the ANSI compliant syntax.
Re: Query regarding Outer Join [message #635378 is a reply to message #635376] Sat, 28 March 2015 10:29 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Lalit.

It was not my intent to start a debate war on the subject (at least, not in this thread). Nor to suggest that anyone was proposing one syntax over the other in this thread; only to try and balance the suggestion of these ideas that seemed to be undertones of what I was reading.

I know there are several people on OraFAQ who really like the additions in sql-92 Join Syntax. But I also know there is just as large a group that is not in any hurry to adopt them.

Kevin

[Updated on: Sat, 28 March 2015 10:30]

Report message to a moderator

Previous Topic: SQL query (merged 3)
Next Topic: sql 9I ,10G ,11GDIFFERENCE SQL LEVEL
Goto Forum:
  


Current Time: Thu Mar 28 08:20:23 CDT 2024