CODEWITHSUNDEEP
sqldatabase
klampo
klampo

Reputation: 135

What is the meaning of the query?

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?

Upvotes: 1

Views: 258

Answers (3)

user330315
user330315

Reputation:

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.

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

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.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311383

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.

Upvotes: 2

Related Questions