title: PostgreSQL: SELECT DISTINCT vs SELECT DISTINCT ON (id)
body: <p>Let many users belong to many groups.</p>
  
  <pre><code>CREATE TABLE users (
      id bigserial PRIMARY KEY,
      username varchar(50) NOT NULL
  );
  
  CREATE TABLE groups (
      id bigserial PRIMARY KEY,
      name varchar(50)
  );
  
  CREATE TABLE group_members (
      group_id bigint NOT NULL REFERENCES groups ON DELETE CASCADE,
      user_id bigint NOT NULL REFERENCES users ON DELETE CASCADE,
      PRIMARY KEY (group_id, user_id)
  );
  </code></pre>
  
  <p>Get the set of all users that belong to groups.</p>
  
  <pre><code>SELECT DISTINCT u.id, username
  FROM groups AS g
  JOIN group_members AS m
  ON m.group_id = g.id
  JOIN users AS u
  ON u.id = m.user_id
  </code></pre>
  
  <p>See <a href="http://sqlfiddle.com/#!15/cd78a0/5" rel="nofollow">this SQL Fiddle</a>.</p>
  
  <p>Would it be faster to use <code>SELECT DISTINCT ON (id)</code> (in the <code>SELECT</code> query above) instead of <code>SELECT DISTINCT</code>?</p>
  
  <p>Both queries return the same result set. I'd rather use <code>SELECT DISTINCT</code> because it's cleaner.</p>
  
  <p>But, imagine the <code>users</code> table had several more columns. Would <code>SELECT DISTINCT</code> be smart enough to just check the <code>id</code> column (instead of all columns) for duplicates?</p>