57508

Oracle outer join with filter condition on the second table

Question:

Is there any condition under which the result sets will be different from the following two statements?

select * from a,b where a.id = b.id and b.name = 'XYZ' select * from a,b where a.id =b.id(+) and b.name = 'XYZ'

I think in both cases it will bring the common rows from a and b where b.name = 'XYZ'. So a.id = b.id(+) has no meaning.

Answer1:

No, there is no condition under which the result sets will be different.

But your assumption "a.id = b.id(+) has no meaning" is not 100% correct. It <em>has</em> a meaning, because it defines the join, otherwise this would be a cartesian product of a and b with all rows from a and b.name = 'XYZ'.

What has no effect is the (+), because the statement is "semantically" wrong. It makes no sense to outer join on id but to join on name.

Usually something like that is wanted:

select * from a,b where a.id =b.id(+) and b.name(+) = 'XYZ';

Short example at <a href="http://www.sqlfiddle.com/#!4/d19b4/15" rel="nofollow">http://www.sqlfiddle.com/#!4/d19b4/15</a>

Recommend

  • Change ios8 Extension Navigation Bar color
  • Oracle: possible encoding problems when importing data
  • share user sessions between Node.js and Clojure
  • how to calculate the values in table view and to display in separate label
  • How to convert blob image into binarybase64 in java?
  • Spring boot application not using jemalloc
  • How to get triad census in undirected graph using networkx in python
  • Inheritance and lazy loading in NHibernate
  • php using msaccess
  • System.IO.IOException: Too many open files
  • Caching of Google Cloud Endpoints?
  • Create intance of any C# class by generic way
  • Facebook Comments on your website via Graph API instead of Comments plugin?
  • What's wrong with my PNG IDAT chunk?
  • How to achieve density/heat map effect in iOS (iPhone/iPad)?
  • unicode characters in html
  • How to create 2 svg's on one page?
  • Create a Terminal-Based Bluetooth Monitor in XCode?
  • WooCommerce get order quantity in thank you page and redirect
  • Working with django : Proxy setup
  • MySQL - Filter records which date is biggest
  • How to make a dependent dropdown in codeigniter
  • Validating a Firebase Key [duplicate]
  • Php artisan optimize is failing on production server
  • How do I set the logging properties in a spring java configuration?
  • How can I filter an array of dictionaries in 'updateSearchResultsForSearchController' to s
  • How to find angle formed by the blades of a wind turbine with respect to a horizontal imaginary axis
  • Java Collections.shuffle() weird behaviour [closed]
  • Ember.js + JQuery-UI Tooltip - Tooltip does not reflect the model / controller changes
  • Separating definition/instantiation of template classes without 'extern'
  • When to use the tag in the head and body section of a html page? [duplicate]
  • openpyxl - adding new rows in excel file with merged cell existing
  • Runtime complexity of getting the length of a string in different representations
  • gnuplot - How to make zmin equal to zmax keeeping autoscale on z axis
  • How to make 100% div height between header and footer?
  • JavaScript Regex to Match Boundaries of Words with diacritics
  • Unity3d lost directional light shadows after generate assetBundle (.unity3d file)
  • How to get rgb from transparent pixel in js
  • Computing the discrete fourier transform of audio data with FFTW
  • Accessing Arguments, Workflow Variables from custom activities