| SQL join [message #641175] |
Tue, 11 August 2015 08:34  |
 |
sanodani
Messages: 98
Registered: October 2014
|
Member |
|
|
Can anyone please explain me, what actually does this (+) sign mean or .. is there any other way to make such join without using inner or outer join?
select a.id, b.name, c.address
from a, b, c
where a.id (+) = b.id
and a.name (+) = b.name
and b.did (+) = c.did
can i write this another way?
|
|
|
|
| Re: SQL join [message #641178 is a reply to message #641175] |
Tue, 11 August 2015 08:39  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
that is an outer join, using the outdated Oracle syntax rather than the ANSI syntax that you should be using in the twentyfirst century.
|
|
|
|
|
|
| Re: SQL join [message #641180 is a reply to message #641175] |
Tue, 11 August 2015 08:44 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
sanodani wrote on Tue, 11 August 2015 14:34is there any other way to make such join without using inner or outer join?
What other type of join did you have in mind?
|
|
|
|
|
|
| Re: SQL join [message #641240 is a reply to message #641175] |
Wed, 12 August 2015 04:59 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
sanodani wrote on Tue, 11 August 2015 19:04Can anyone please explain me, what actually does this (+) sign mean
As others have said, it is old Oracle join syntax.
One of the good reasons to use ANSI syntax over the old Oracle join syntax is that, there are nil chances of accidentally creating a cartesian product. With more number of tables, there is a chance to miss an implicit join with older Oracle join syntax, however, with ANSI syntax you cannot miss any join as you must explicitly mention them.
Difference between Oracle outer join syntax and the ANSI/ISO Syntax.
LEFT OUTER JOIN -
SELECT e.last_name,
d.department_name
FROM employees e,
departments d
WHERE e.department_id = d.department_id(+);
SELECT e.last_name,
d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
RIGHT OUTER JOIN -
SELECT e.last_name,
d.department_name
FROM employees e,
departments d
WHERE e.department_id(+) = d.department_id;
SELECT e.last_name,
d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
FULL OUTER JOIN -
Before the native support of hash full outerjoin in 11gR1, Oracle would internally convert the FULL OUTER JOIN the following way -
SELECT e.last_name,
d.department_name
FROM employees e,
departments d
WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT NULL,
d.department_name
FROM departments d
WHERE NOT EXISTS
(SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
SELECT e.last_name,
d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
Read the documentation for more about Joins.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
