title: Does direction of join condition matter in left joins?
body: <p>There are two postgres tables with roughly the following structures:</p>
  <pre><code>products
  +----+-----------+
  | id | rawDataId |
  +----+-----------+
  | 1  |     a     |
  +----+-----------+
  | 2  |     null  |
  +----+-----------+
  | 3  |     b     |
  +----+-----------+
  | 4  |     null  |
  +----+-----------+
  | 5  |     c     |
  +----+-----------+
  
  rawData
  +----+-----------+
  | id | productId |
  +----+-----------+
  | a  |     1     |
  +----+-----------+
  | b  |     3     |
  +----+-----------+
  | c  |     5     |
  +----+-----------+
  | d  |     6     |
  +----+-----------+
  | e  |     7     |
  +----+-----------+
  | f  |     8     |
  +----+-----------+
  | g  |     9     |
  +----+-----------+
  </code></pre>
  <p>While FK constraints were not properly defined when these were set up, in application logic <code>products.rawDataId</code> maps to <code>rawData.id</code> and <code>rawData.productId</code> maps to <code>products.id</code>.
  Each record in <code>products</code> can have 1 or 0 records in <code>rawData</code>, meanwhile, there are some orphaned records in <code>rawData</code> whose associated <code>products</code> records have been deleted.</p>
  <p>I'm trying to find all orphaned <code>rawData</code> records by doing a left join. However, I get different number of rows depending on my join condition:</p>
  <pre><code>psql&gt; SELECT count(*)
         FROM &quot;rawData&quot; &quot;r&quot;
         LEFT JOIN &quot;products&quot; &quot;p&quot;
         ON &quot;r&quot;.&quot;productId&quot; = &quot;p&quot;.&quot;id&quot;
         WHERE &quot;p&quot;.&quot;id&quot; IS NULL;
  
  psql&gt; 7000
  
  
  psql&gt; SELECT count(*)
         FROM &quot;rawData&quot; &quot;r&quot;
         LEFT JOIN &quot;products&quot; &quot;p&quot;
         ON &quot;r&quot;.&quot;id&quot; = &quot;p&quot;.&quot;rawDataId&quot;
         WHERE &quot;p&quot;.&quot;id&quot; IS NULL;
  
  psql&gt; 9239
  </code></pre>
  <p>These tables have ~100k rows, so it's not easy to traverse them manually, but I was wondering under what scenarios I would be getting different results based on the join condition. Should they not return the same number of rows?</p>
  <p>Are there any other queries I can execute to find the diff, or get the number of orphaned rows more accurately, given these table structures and constraints?</p>
  <p>The output I would like to get, based on the sample tables is:</p>
  <pre><code>+----+-----------+------+-----------+
  | id | productId |  id  | rawDataId |
  +----+-----------+------+-----------+
  | d  |     6     | null |   null    |
  +----+-----------+------+-----------+
  | e  |     7     | null |   null    |
  +----+-----------+------+-----------+
  | f  |     8     | null |   null    |
  +----+-----------+------+-----------+
  | g  |     9     | null |   null    |
  +----+-----------+------+-----------+
  </code></pre>