Next Oct 18 PreviousThe 7th son of the 7th son
Suppose we have a database table PERSON that contains the data of fathers and their children. Each record has an ID field and if a record contains a child it has a reference to its father through a FATHER_ID field.
Which query will give you a list of all children with their fathers.
A: SELECT * FROM PERSON P1 LEFT OUTER JOIN PERSON P2 ON P1.ID = P2.FATHER_ID;
B: SELECT * FROM PERSON P1, PERSON P2 WHERE P1.ID = P2.FATHER_ID;
C: SELECT * FROM PERSON P1 NATURAL JOIN PERSON P2;
D: You can't join a table with itself.
Answer
Answer A is a left outer join which means that it will find all of the left side (all fathers and children) and try to match these with the right side or to use NULL if no match is found. So this query finds all fathers with their children's data (= OK) and all children with NULL data (= not OK). Therefore this anwser is not correct. Some sample output of this query might look like:
+------+------+-----------+------+------+-----------+
| ID | NAME | FATHER_ID | ID | NAME | FATHER_ID |
+------+------+-----------+------+------+-----------+
| 1 | Joe | NULL | 2 | Suzy | 1 |
| 1 | Joe | NULL | 3 | Pete | 1 |
| 2 | Suzy | 1 | NULL | NULL | NULL |
| 3 | Pete | 1 | NULL | NULL | NULL |
+------+------+-----------+------+------+-----------+
So we need a join that is more restrictive and that will find only the rows where a match between P1.ID and P2.FATHER_ID exists. An inner join will do so and because a correct syntax for an inner join is given in answer B this answer is correct. Although this syntax is not very commonly used by programmers it definitely the syntax construct that DBA's and the more mathematical inclined will prefer. The output might look like:
+------+------+-----------+------+------+-----------+
| ID | NAME | FATHER_ID | ID | NAME | FATHER_ID |
+------+------+-----------+------+------+-----------+
| 1 | Joe | NULL | 2 | Suzy | 1 |
| 1 | Joe | NULL | 3 | Pete | 1 |
+------+------+-----------+------+------+-----------+
It's no problem to join a table with itself so answer D is false. Some refer to it as a 'self join'i>. A little tip: when working with SQL don't even think of (the underlying) tables, just try to think of matching sets. It might sound strange but it will really help you in the end.
A natural join is a bit of an exotic join: it is a join where an implicit WHERE clause is used based on the table names. When natural joining a table with itself it will basically find itself and that will not give us the desired result so this anwer is not correct too.
