Oracle Operator

Question

I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them   The DBMS is Oracle  I did not understand a statement which read like this   select     from a b where a id b id      I am confused about the     operator  and could not get it at any forums     searching for   within quotes didn t work either    Anyway  I used  Explain Plan  of SQLDeveloper and I got an output saying that HASH JOIN  RIGHT OUTER  etc   Would there be any difference if I remove the     operator at the end of the query  Does the database have to satisfy some condition  like having some indexes  etc   before     can be used   It would be greatly helpful if you can provide me a simple understanding  or some good links where I can read about this   Thanks

User · Accepted Answer

That s Oracle specific notation for an OUTER JOIN  because the ANSI-89 format  using a comma in the FROM clause to separate table references  didn t standardize OUTER joins   The query would be re-written in ANSI-92 syntax as      SELECT          FROM a LEFT JOIN b ON b id   a id   This link is pretty good at explaining the difference between JOINs     It should also be noted that even though the     works  Oracle recommends not using it      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

User · Answer

In Oracle      denotes the  optional  table in the JOIN  So in your query   SELECT a id  b id  a col 2  b col 2      FROM a b WHERE a id b id      it s a LEFT OUTER JOIN of table  b  to table  a   It will return all data of table  a  without losing its data when the other side  optional table  b   has no data     The modern standard syntax for the same query would be  SELECT  a id  b id  a col 2  b col 2      FROM a LEFT JOIN b ON a id b id   or with a shorthand for a id b id  not supported by all databases    SELECT  a id  b id  a col 2  b col 2      FROM a LEFT JOIN b USING id    If you remove     then it will be normal inner join query  Older syntax  in both Oracle and other databases   SELECT  a id  b id  a col 2  b col 2      FROM a b WHERE a id b id   More modern syntax   SELECT  a id  b id  a col 2  b col 2      FROM a INNER JOIN b ON a id b id   Or simply   SELECT  a id  b id  a col 2  b col 2      FROM a JOIN b ON a id b id     It will only return all data where both  a   amp   b  tables  id  value is same  means common part    If you want to make your query a Right Join  This is just the same as a LEFT JOIN  but switches which table is optional     Old Oracle syntax   SELECT  a id  b id  a col 2  b col 2      FROM a b WHERE a id    b id   Modern standard syntax   SELECT  a id  b id  a col 2  b col 2      FROM a RIGHT JOIN b ON a id b id   Ref  amp  help   https   asktom oracle com pls asktom f p 100 11     P11 QUESTION ID 6585774577187  Left Outer Join using   sign in Oracle 11g  https   www w3schools com sql sql join left asp

User · Answer

The     operator indicates an outer join  This means that Oracle will still return records from the other side of the join even when there is no match  For example if a and b are emp and dept and you can have employees unassigned to a department then the following statement will return details of all employees whether or not they ve been assigned to a department   select   from emp  dept where emp dept id dept dept id      So in short  removing the     may make a significance difference but you might not notice for a while depending on your data

User · Answer

In practice  the   symbol is placed directly in the conditional statement and on the side of the optional table  the one which is allowed to contain empty or null values within the conditional

[sql] Oracle "(+)" Operator

Examples related to sql

Examples related to oracle

Examples related to join

Examples related to outer-join