Tag Archives: relational databases

Unit 10: SQL and relational databases

Learning SQL was not conceptually difficult for me, mainly because of my past experiences with relational databases. The different kinds of joins are also not difficult for me because I understand set theory. The inner/outer/full joins are really references to a Venn diagram. See the above diagram, which is based on the exercise Bruce had us do in the Images database.

The left (red) oval is the table images. The right (blue) oval is the table photographers. As the diagram shows, some images have no associated photographer. Some photographers have no associated images. The part where the two ovals intersect consists of those images that have associated photographers (or those photographers that have associated images).

So an INNER JOIN between these two tables would return those rows in the overlapping (inner) part of the diagram: those rows that have both a photographer and an image. A LEFT OUTER JOIN contains all the image records in the left (red) oval, including those that do not have a photographer as well as those who do. A RIGHT OUTER JOIN contains all the photographer records in the right (blue) oval, including those that have no images. A FULL JOIN will return all the records in both ovals.

Getting the syntax right is the hardest part of SQL, and getting the right results is mainly dependent upon having normalized tables and understanding the table structure in a database.

Leave a comment

Filed under LAMP architecture, SIRLS 672