Joins Defined
A join is a SELECT command that combines the columns and rows from two or more tables.
Why use joins?
Through the process of database design and normalization (that will come later) we have formed tables that are related to each other that allows for scalability and elimination redundancy. But all of this related data is in different tables. We need to have a way to bridge it all back together to provide good INFORMATION. For instance, if a Professor wants to know all of the classes he is teaching, he would need to ‘join’ the Instructor table to the Course table based on our design for our sample problem. In other words, as a Professor, I want a list of all of the courses I am teaching.
To get this information, I would join the two tables together based on the Primary Key of one table and the Foreign Key of another. I would do all of this in a Select Command.
To accomplish this, I would write the following join:
SELECT TInstructors.strFirstName, TInstructors.strLastName, TCourses.strCourseName 1
FROM
TInstructors 2
,TCourses 3
WHERE
AND TInstructors.intInstructorID = TCourses. intInstructorID 4
In line #1 above….I am selecting specific columns from specific tables. Note, I can choose columns from every table I actually join. To do this, preface the column with the table name (i.e. TInstructors.strFirstName).
In line #2 and #3 above…I am now telling the Select statement to use the following tables.
In line #4 above….I am now telling the Select Statement to join these tables based on the Parent Table’s primary key and the Child Table’s related foreign key.
That is all there is to it. The art is to know where the data is (meaning what tables hold the data I need), and how the relationships between those tables work. We will start slow on this and by the end of the semester you will have had a ton of practice and actually create many sophisticated joins that may join up to 8 – 10 tables. But let’s get there one step at a time.
NOTE: There are other ways of writing Joins. If you go to www.w3schools.com and look at Joins, you will notice a totally different syntax using the ON clause. We will get to that. Right now, I believe this is the simplest way of being introduced to joins and testing the relationships we have established in our database thus far.
The post process of database design and normalization appeared first on My Assignment Online.