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 |
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 |
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 |
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 |
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 |
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 |
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 #635376 is a reply to message #635368] |
Sat, 28 March 2015 09:55 |
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 |
|
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
|
|
|
Goto Forum:
Current Time: Thu Mar 28 08:20:23 CDT 2024
|