title: SQL: Understanding how SELECT DISTINCT eliminates duplicates
body: <p>I have the following table called genkeyword:</p>
  
  <pre><code>---------------------------------------------------------------------------
  |  id     |      title           |   genre       | keyword      |    year |
  ----------------------------------------------------------------------------
  | 315     |  Harry Potter        |   drama       | magic        |   2011  |
  | 315     |  Harry Potter        |   mystery     | magic        |   2011  |
  | 315     |  Harry Potter        |   adventure   | magic        |   2011  |
  | 315     |  Harry Potter        |   fantasy     | magic        |   2011  |
  | 315     |  Harry Potter        |   drama       | witch        |   2011  |
  | 315     |  Harry Potter        |   mystery     | witch        |   2011  |
  | 315     |  Harry Potter        |   adventure   | witch        |   2011  |
  | 315     |  Harry Potter        |   fantasy     | witch        |   2011  |
  | 407     |  Cinderella          |   fantasy     | prince       |   2015  |
  | 407     |  Cinderella          |   drama       | prince       |   2015  |
  | 407     |  Cinderella          |   fantasy     | prince       |   2015  |
  | 407     |  Cinderella          |   drama       | prince       |   2015  |
  | 826     |  The Shape of Water  |   horror      | scientist    |   2017  |
  | 826     |  The Shape of Water  |   adventure   | scientist    |   2017  |
  | 826     |  The Shape of Water  |   thriller    | scientist    |   2017  |
  | 826     |  The Shape of Water  |   drama       | scientist    |   2017  |
  | 826     |  The Shape of Water  |   horror      | friendship   |   2017  |
  | 826     |  The Shape of Water  |   adventure   | friendship   |   2017  |
  | 826     |  The Shape of Water  |   thriller    | friendship   |   2017  |
  | 826     |  The Shape of Water  |   drama       | friendship   |   2017  |
  ---------------------------------------------------------------------------
  </code></pre>
  
  <p>I have the following query, which gets the frequency of all the genres each movie in the above table has in common with Harry Potter:</p>
  
  <pre><code>select title, year, count(distinct genre) as genre_freq from genkeyword
  where genre in (select genre from genkeyword where title='Harry Potter') and 
  title &lt;&gt; 'Harry Potter' group by 
  title, year order by genre_freq desc;
  </code></pre>
  
  <p>The output should be:</p>
  
  <pre><code>--------------------------------------------------
  | title                |    year   |    genre_freq |
  ---------------------------------------------------
  | Cinderella           |    2015   |      2        |
  | The Shape of Water   |    2017   |      2        |
  ----------------------------------------------------
  </code></pre>
  
  <p>I am having trouble understanding exactly how count(distinct genre) in the query works however. I know that SELECT DISTINCT returns only distinct values and eliminates duplicate records from the results. I am not sure when the count(distinct genre) actually removes the duplicate records. I really would like to understand what the query is doing behind the scenes.</p>
  
  <p>What I know so far:</p>
  
  <p>For each tuple in genkeyword:</p>
  
  <ul>
  <li>The 'where genre in (select genre from genkeyword where title='Harry Potter')', retrieves all the rows in which the value of the genre attribute is a genre in Harry Potter.</li>
  <li>If the genre in the tuple that is being considered is in the result set returned by the where clause, then it is counted by count(distinct genre). Also the movie value in the tuple being considered can't be Harry Potter, or else it won't be counted.</li>
  </ul>
  
  <p>However, when does the count(distinct genre) actually remove duplicates? Any insights are appreciated.</p>