How tables share a relationship, part 2 - SQL Tutorial

- [Host] Now let's take a closer look at the result that our join has produced. There are a few things that we can observe, now that we've combined the invoice table to the customer table. Now, if we take a look at the invoice ID field from the invoice section of the results set, we can see that the first seven records are linked to the same customer. This is customer id number one. This link tells us that customer id number one is responsible for generating all seven of these invoices. If we scroll over to the customer portion of our result, we can tell who customer id number one is. And in this case, it's Mr. Lewis G. One customer is linked to many invoices. In the language of relational databases, we can say that the customer table has a one to many relationship with the invoice table. Now this makes sense when you think about it, seeing that a single customer can generate many invoices simply by purchasing multiple songs. We can observe the relationship between two tables of a relational database by looking at another way. And this is looking at what's referred to as a entity relationship diagram. Let's now take a look at the ERD. Now, if we take a look at the diagram in front of us, it's referred to as an ERD, or an entity relationship diagram. And what it is, is a graphical representation of the relationship between our tables. Now, if we take a look at the customer table and the invoice table, we can observe that there is indeed a relationship that is demonstrated by this line that is connecting them. If we take a look inside of the customer table, we do see that primary key field, designated by that key symbol next to the customer id field. In the invoice table, we see the primary key is the invoice id, but we do see that customer id field also in the invoice table. Now, to officially refer to this particular field, the customer id field, it is referred to as a foreign key. So the customer table is connected to the invoice table by means of a primary foreign key connection. And again, this symbol here shows that one customer can have many invoices. Now this is how an ERD demonstrates the relationship between multiple tables. If we take a look at the rest of our ERD, we can see many other instances of primary and foreign key relationships. Now joins would not be necessary if the invoice table had included all of the customer names. Now, why was this not done? If we go one step further, instead of a database with 13 tables, we can have just one gigantic table with all of the information that we have displayed here. Why don't we do this? Well, the answer to this is, in a relational database, the process of distributing fields across related tables is known as normalization. And normalization keeps the size of our database small, as it reduces the need to duplicate or make mention of data in multiple places. Okay, now this becomes significant when saving seconds of processing time when we construct our queries. Considering the staggering size of some databases, every second does indeed count. Imagine if your Google search took five minutes rather than a few seconds. Now that we have identified the common link between the two related fields in both the invoice and customer table, let's take a closer look at how we would write join syntax that would be a little more efficient.