What is the meaning of the query?

Question

GUYS I KNOW (+) MEANS OUTER JOIN

SELECT *
FROM email a,email_types b
WHERE a.user_id  (+)     =10
  AND a.email_id(+)=b.email_enum;

SELECT *
FROM email a,email_types b
WHERE a.user_id      =10
  AND a.email_id(+)=b.email_enum;

What is the meaning of a.user_id (+) = 10? Is this equal to a.user_id =10, or do they have a different meaning?

What is the difference between the two queries?

a.user_id (+) =10 is matched with which column b. The value 10 is matched with column of b table?

It's Oracle's synonym for OUTER JOIN. http://stackoverflow.com/questions/517557/meaning-of-in-sql-queries — c4pone, Jun 10 '15 at 06:53
Implicit outer Join in oracle http://beginner-sql-tutorial.com/sql-joins.htm U may understand here ... — User Learning, Jun 10 '15 at 07:09

score 2 · Answer 1 · answered Jun 10 '15 at 06:54

2

The (+) syntax is Oracle's old, outdated, syntax for an implicit outer join. The side with the (+) is the side that may not have matches, so this query will return all records with b with their counterparts in a where a.user_id = 10, or with nulls if there's no matching a record.

answered Jun 10 '15 at 06:54

Mureinik

297,002
52
306
350

a.user_id (+) =10 is matched with which column b. The value 10 is matched with column of b table. – klampo Jun 10 '15 at 07:07
And do the two queries behave differently? – Stefan Steinegger Jun 10 '15 at 07:15

score 0 · Answer 2 · answered Jun 10 '15 at 07:42

The (+) in the first join condition is required to ensure that the join is treated as an outer join as detailed in the Oracle docs:

If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.

The key thing to note here is that without that (+) in every join condition, you will end up with the results of an inner join, not an outer join.

score 0 · Accepted Answer · answered Jun 10 '15 at 07:58

This is best explained by converting the Oracle syntax to standard SQL:

SELECT *
FROM email a,email_types b
WHERE a.user_id  (+)     =10
  AND a.email_id(+)=b.email_enum;

is equivalent to:

SELECT *
FROM email_types b
  LEFT JOIN email a ON a.email_id = b.email_enum AND a.user_id = 10;

Which means "do an outer join between email_types and email but only consider rows from the email table where user_id equals 10". It will return all rows from email_types and all matching emails (if there are any) from user_id = 10 if there are no emails for that user, then no rows from the email table are considered.

If you remove the (+) operator from the original query's condition on the user_id column, it would be equivalent to:

SELECT *
FROM email_types b
  LEFT JOIN email a
         ON a.email_id = b.email_enum 
WHERE a.user_id = 10;

which would result in an inner join because of the condition on the outer joined table.

What is the meaning of the query?

3 Answers3