title: Remove Duplicates From JOIN
body: <p>I'm currently learning databases in school right now, but unfortunately our teacher doesn't really enjoy helping or answering questions, at all. I'm currently working on a couple of Oracle DB exercises right now and I've come across one question I really don't know how to solve.</p>
  
  <p><strong>Table 1: Students</strong> </p>
  
  <pre><code>   ID      FAMILY NAME     FIRST NAME    BIRTH DATE      IM_DATE    FACULTY
  </code></pre>
  
  <hr>
  
  <pre><code>  4711    Lehmann          Heini         13.03.89       01.09.08     I         
    4712    Huber            Sven          14.07.89       01.09.08     IWI       
    4713    Meier            Swantje       11.04.88       01.03.09     IWI       
    4714    Tunix            Ole           15.03.88       01.03.09     IWI       
    4715    Kannix           Peter         02.11.89       01.03.09     IWI       
    4716    Weissnix         Axel          15.12.88       01.03.09     IWI     
  </code></pre>
  
  <p><strong>Table 2: LN</strong></p>
  
  <pre><code>   ID    FKBEZ         VNR P_DATE        GRADE
  </code></pre>
  
  <hr>
  
  <pre><code>  4711   DB1            1 02.02.08        4,7 
    4711   DB1            2 07.07.09          5 
    4711   PR1            1 28.01.09          3 
    4712   DB1            1 02.02.08        3,7 
    4713   DB1            1 02.02.08        1,7 
    4713   DB2            1 02.02.09        3,7 
    4714   PR1            1 28.01.09          2 
    4715   DB1            1 02.02.08          5 
    4711   DB2            1 14.07.09        1,3 
    4711   PR2            1 30.06.09        2,3 
  </code></pre>
  
  <p>Now, here are the questions.</p>
  
  <p><strong>Q1:</strong> Create an SQL query (JOIN) which will result in duplicate rows.
  <strong>Q2:</strong> Change your query from Q1 so now it won't show any duplicates.</p>
  
  <p>My first 'problem' here is that I'm not 100% sure what the definition of duplicate is. Are duplicates rows with 100% identical content on ALL columns even if you haven't selected them in your SELECT command?</p>
  
  <p>Example:
  Say I've created a query and I've chosen the columns 'Family Name' and 'Age' in my SELECT command and my result looks like this:</p>
  
  <pre><code>Family Name          Age
  </code></pre>
  
  <hr>
  
  <pre><code>Miller               20
  Miller               20
  </code></pre>
  
  <p>but these are actually two different people and have a different first name. Do these qualify as duplicates, since I haven't selected the first name and therefore it isn't showing, or doesn't it matter what I choose via SELECT and duplicate rows only qualify as duplicates if they're 100% identical?</p>
  
  <p>Alright, back to my questions.
  For <strong>Q1</strong> I've chosen a simple (INNER) JOIN query looking like this</p>
  
  <p>SELECT S.ID, S.Family_Name, S.First_Name, LN.FKBEZ
  FROM Students S
  JOIN LN
  ON S.ID = LN.ID</p>
  
  <pre><code>FAMILY NAME               FIRST NAME               FKBEZ       (GRADE)
  </code></pre>
  
  <hr>
  
  <pre><code>Lehmann                   Heini                     DB1          4,7 
  Lehmann                   Heini                     DB1            5 
  Lehmann                   Heini                     PR1            3 
  Huber                     Sven                      DB1          3,7 
  Meier                     Swantje                   DB1          1,7 
  Meier                     Swantje                   DB2          3,7 
  Tunix                     Ole                       PR1            2 
  Kannix                    Peter                     DB1            5 
  Lehmann                   Heini                     DB2          1,3 
  Lehmann                   Heini                     PR2          2,3 
  </code></pre>
  
  <p>This is the result. I haven't SELECTed 'GRADE' in my query, but I've listed it for you, too, so you can understand my question a little better.
  Now, since I haven't selected 'GRADE' in my query, I'd consider row 1 + 2 to be duplicates, because they're identical in every <strong>visible</strong> column. I then went on to Q2 and used the exact same query, only this time using a NATURAL JOIN (since this would eliminate all the duplicate rows), but the result has been exactly the same.</p>
  
  <p>So now my conclusion is that rows are only considered being duplicates if they're 100% identical on every <strong>visible</strong> and <strong>'invisible'</strong> column.
  But now I'm actually completely stumped, because I have no idea how to solve Q1 + Q2.</p>
  
  <p>It's important to know, that we're not supposed to solve these questions using DISTINCT or GROUP BY. Only (different kinds of) JOINS, INTERSECT, UNION and MINUS.</p>
  
  <p>I guess you can see that I've put a lot of time and effort in composing this post so I'd appreciate it very much, if you guys could help me out on this one.</p>
  
  <p>Thanks.</p>