title: How to do LEFT JOIN with double condition in Oracle syntax?
body: <p>I have 2 tables.</p>
  
  <p>1) <strong>car</strong> table:</p>
  
  <p><a href="https://i.stack.imgur.com/KJCKx.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KJCKx.jpg" alt="car table"></a></p>
  
  <p>2) <strong>param</strong> table:</p>
  
  <p><a href="https://i.stack.imgur.com/adWdX.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/adWdX.jpg" alt="param table"></a></p>
  
  <p>I need to fetch duplicate cars have <em>owner</em> params match and do NOT differ in <em>insurance</em> (that's it must be either the same or is <strong>absent for both</strong>).</p>
  
  <p>I'm successfully executing my query with <code>LEFT JOIN</code> in ANSI-syntax:</p>
  
  <pre><code>SELECT owner.name, owner.value, COALESCE (insur.name, 'insurance'), insur.value, count(*)
  FROM car INNER JOIN param owner ON car.id = owner.car_id
  LEFT JOIN param insur ON car.id = insur.car_id AND insur.name = 'insur'
  WHERE owner.name = 'owner'
  GROUP BY owner.name, owner.value, insur.name, insur.value
  HAVING count(*) &gt; 1
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!4/0eb96/7/0" rel="nofollow noreferrer">SQL Fiddle with properly working ANSI-syntax</a></p>
  
  <p>But when I rewrote this query with <code>(+)</code> sign from Oracle syntax instead <code>LEFT JOIN</code> I have different result:</p>
  
  <pre><code>SELECT owner.name, owner.value, COALESCE (insur.name, 'insurance'), insur.value, count(*)
  FROM car,
       param owner,
       param insur
  WHERE car.id = owner.car_id
    AND owner.name = 'owner'
    AND car.id (+) = insur.car_id -- key change
    AND insur.name = 'insur'
  GROUP BY owner.name, owner.value, insur.name, insur.value
  HAVING count(*) &gt; 1
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!4/0eb96/11" rel="nofollow noreferrer">SQL Fiddle with unexpected result of (+) instead LEFT JOIN</a></p>
  
  <p>Result of this query is empty. I don't understand how to rewrite it in Oracle syntax to have the same result of queries.</p>