CODEWITHSUNDEEP
sqloracle-databaseouter-joinfull-outer-join
wali
wali

Reputation: 239

SELECT * FROM tableA, tableB WHERE Conditions [+]

I have the following query

SELECT * 
FROM tableA, tableB
WHERE Conditions [+]

What does this keyword Conditions[+] Stands for? How this query behaves as a outer join?

Upvotes: 0

Views: 3439

Answers (4)

Taryn
Taryn

Reputation: 247670

That is old Oracle Join syntax. 

SELECT * 
FROM tableA, tableB
WHERE Conditions [+] -- this should be tableA (+) = tableB

The positioning of the + sign denotes the JOIN syntax.

If you query was:

SELECT * 
FROM tableA, tableB
WHERE tableA.id (+) = tableB.Id

Then it would be showing a RIGHT OUTER JOIN so the equivalent is:

SELECT * 
FROM tableA
RIGHT OUTER JOIN tableB
   ON tableB.id = tableA.Id

If the + sign was on the other side then it would be a LEFT OUTER JOIN

SELECT * 
FROM tableA, tableB
WHERE tableA.id  = tableB.Id (+)

is equivalent to 

SELECT * 
FROM tableA
LEFT OUTER JOIN tableB
   ON tableA.id = tableB.Id

I would advise using standard join syntax though.

If you do not specify a + sign then it will be interpreted as an INNER JOIN

SELECT * 
FROM tableA, tableB
WHERE tableA = tableB

it's equivalent is:

SELECT * 
FROM tableA
INNER JOIN tableB
    ON tableA.id = tableB.id

A FULL OUTER JOIN would be written using two SELECT statements and a UNION:

SELECT * 
FROM tableA, tableB
WHERE tableA.id  = tableB.Id (+)
UNION
SELECT * 
FROM tableA, tableB
WHERE tableA.id (+) = tableB.Id

It's equivalent is:

SELECT * 
FROM tableA
FULL OUTER JOIN tableB
    ON tableA.id = tableB.id

Here is a tutorial that explains a lot of these:

Old Outer Join Syntax

Upvotes: 6

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

I hate to just copy & paste an answer, but this sort of thing can be found pretty easily if you do a little searching...

An outer join returns rows for one table, even when there are no matching rows in the other. You specify an outer join in Oracle by placing a plus sign (+) in parentheses following the column names from the optional table in your WHERE clause. For example:

SELECT ut.table_name, uc.constraint_name 
FROM user_tables ut, user_constraints uc 
WHERE ut.table_name = uc.table_name(+);

The (+) after uc.table_name makes the user_constraint table optional. The query returns all tables, and where there are no corresponding constraint records, Oracle supplies a null in the constraint name column.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

It is not important how that behaves. You should use the standard syntax for outer joins:

select *
from tableA left outer join
     tableB
     on . . .

The "(+)" syntax was introduced by Oracle before the standard syntax, and it is highly out of date.

Upvotes: 3

Caffeinated
Caffeinated

Reputation: 12478

'Conditions' here just means what you're using to filter all this data.

LIke here's an example:

SELECT * 
FROM tableA, tableB
WHERE Name like '%Bob%'

would return names that have "Bob" anywhere inside.

About outer joins, actually you'd use that in the FROM clause:

So maybe

SELECT * 
FROM tableA ta 
     OUTER JOIN tableB tb
     ON ta.name = tb.name
WHERE ta.age <> 10

and there where here is optional, by the way

Upvotes: 0

Related Questions