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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s