title: Best way to eliminate duplicates rows after multiple joins
body: <p>I'll consider three simple tables. A, B are my entity tables and C is an intermediate table that creates a many-to-many relationship between A &amp; B.</p>
  <p>Schemas:</p>
  <pre><code>A: (id INTEGER PRIMARY KEY)
  B: (id INTEGER PRIMARY KEY)
  C: (
    A_id INTEGER, 
    B_id INTEGER, 
    FOREIGN KEY(A_id) REFERENCES A(id), 
    FOREIGN KEY(B_id) REFERENCES B(id)
  )
  </code></pre>
  <p>Now, consider the below query</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
    A.id
  FROM A
  LEFT OUTER JOIN C
    ON (A.id = C.A_id)
  LEFT OUTER JOIN B
    ON (C.B_id = B.id)
  WHERE ...;
  </code></pre>
  <p>This query would result in duplicate values of A.id, which is expected because C might have multiple rows associated with each row of A. My question is what's the best way to eliminate these duplicates and get the A records. I only need the A records.</p>
  <p>I am aware of two ways,</p>
  <pre class="lang-sql prettyprint-override"><code>-- Using DISTINCT
  SELECT
    DISTINCT(A.id), ...
  FROM A
  LEFT OUTER JOIN C
    ON (A.id = C.A_id)
  LEFT OUTER JOIN B
    ON (C.B_id = B.id)
  WHERE ...
    ORDER BY A.id;
  </code></pre>
  <p>And</p>
  <pre class="lang-sql prettyprint-override"><code>-- Or using A.id IN (above query)/ A.id = Any(above query)
  SELECT 
    ...
  FROM A 
  WHERE A.id IN (
    SELECT
      A.id
    FROM A
    LEFT OUTER JOIN C
      ON (A.id = C.A_id)
    LEFT OUTER JOIN B
      ON (C.B_id = B.id)
    WHERE ...
  );
  </code></pre>
  <p>I'm using PostgreSQL. I need to include all the tables for filtering, so not joining a table cannot be considered as an improvement. I've analyzed both the queries but I still feel there might be a better way to do this(in terms of performance).</p>
  <p>Any help is really appreciated!</p>