From the course: SQL Essential Training

Simplifying JOINs - SQL Tutorial

From the course: SQL Essential Training

Start my 1-month free trial

Simplifying JOINs

“

- [Instructor] So we've seen from our example that's in front of us, our first example that joins have a special syntax when referring to field names. Since two tables in any given database may have fields with the identical names, when creating joins, we must specify the table name followed by the column name that we would like to join so that the SQL browser knows exactly which version of the field we are referencing. Now this syntax requires that the table name be listed first followed by the column or field name. Now joins are often used with aliases just as we visited prior. Now we introduce aliasing to reduce the amount of typing that's required when we're building out a SQL statement with a join. Let's visit how we would do this. Now I'm going to make some alterations that's going to be more in line with a real world query when we are building a SQL statement with a join. Here we have the invoice, and I'm just going to do as we do a column name and introduce the keyword as, and because this is invoice, let's call this I. Down here in the customer area, let's alias this customer table as C. Let's revisit our syntax that comes after the on keyword. We have the invoice table followed by the customer ID field. Because we have just aliased the invoice table as I, instead of fully typing out invoice, I simply have to say I at this point. The same thing can occur for the customer table, which is also aliased as C. So instead of typing out full customer, I just simply put C. As you would suspect down here in the order by, I would also say C.customerid. Now as we mentioned before, the star returns all of the records from all of the tables. And while this is great for our demonstration of the concept, in a real world scenario, we would want to specify the fields that we are interested in. So our request wants to show the invoices along with the customers that generated them. So let's start introducing some of the fields that are relevant to our request. Let's start with the customer last and first name. At this point, I just need to put a C, meaning I would like to start referencing fields from my customer table. And because I've aliased this table as C, in my select I'm also going to just specify C to mean customer table. I put a period and then I say last name. I'm going to put a comma, and I also want the customer's first name. Okay, I'm going to introduce another comma, and I'm going to say I want a few fields from my invoice table now. To reference fields from my invoice table, I place my alias, which is I, and then I want to see my invoice id. I'll specify this. I also want to include the customer id, but this time from the invoice table. And I'm also going to include the invoice date. So I do, I.invoicedate. And last but not least, I want the total which also resides in the invoice table. Now in looking at the full syntax here, I have now aliased the syntax that we previously had. And it's very important because we have two tables, and as mentioned, in any given two tables, we may have a field that's named exactly the same thing. And I have done so by introducing as I next to the invoice, as C, and everywhere else that I'm referencing a field from either the customer or invoice table, I'm proceeding that with a C or an I, followed by a period and then listing out the name of that field. Now let's now rerun our query and observe the result. We have a much cleaner looking query that now includes only the fields that we are interested in. We have fields from both the customer and invoice tables. We're satisfying the request that says please list all of the customers together with their associated invoices. We can tell when those invoices were generated and the amounts of each. And hereby we have effectively responded to a request that is now giving us a view into both the customer and invoice tables. Well done.

Contents