Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
After awhile you are going to find that be very straightforward SQL statements, like this one where I'm simply selecting three pieces of data from the Product table, well they don't get all that fantastically useful. What I'm retrieving here is the name of a product, the color of the product, and the ProductCategoryID, which in this case is 18 or 27 or 23, which is not all that helpful if I'm just reading this data. I want to know what the category is. Well I know that my database has the category stored in it but it's not in this table and here's why. I have got a simple diagram that's just showing these parts of this AdventureWorksLT database.
There is a Product table with all these columns in and then there a ProductCategory table and this is simply good normalization. We can have multiple products in category, so it's a good idea to take that category data out and put it in its own table. And what this really means for MySQL statement is I want one SQL statement to be retrieving data from two different tables or even more potentially. And we do this typically through what's called a join.
We are joining two tables together to get at data in both of them. Now how we do that? Well, rather than just say I won't retrieve information from SalesLT.Product, I'm going to use the word JOIN and say I'm also going to retrieve information from SalesLT.ProductCategory. Now, as soon as I do this, we are going to start getting these little squiggly underlines here, that it's looking at it and saying, "Well, Name is now ambiguous and ProductCategoryID is now ambiguous." Meaning that if I look at those definitions of the tables, we have the Name in the Product table and the Name in the ProductCatagory table.
We have a ProductCategoryID in one and ProductCategoryID in the other. So SQL Server Management Studio is already telling us there's a problem. You need to get a bit more specific here. Now the way we deal with this first problem is we have to say okay, well SalesLT.Product, I'm going to just put the letter "a" after it and after my second table I am going to put the letter "b". Now the letters aren't important. You could pick a, b, p, c. It's up to you. "a" and "b" is pretty common and then because you're naming the tables, you will actually say well, if I have named the first one "a" I am going to say that I am interested in a.Name.
That is now no longer ambiguous and thus quickly disappear. It says "Okay, you want a.Name so I am looking for the a table" and in this case I want a.ProductCategoryID, just to keep everything similar right now. So that gets us pass the ambiguous column names but then what? Well I could try and execute this, but the problem is its saying I have got an issue here. I have incorrect syntax and really what it's complaining about is you are telling him to join these two tables and it doesn't know how.
Now depending on which database background you come from, you might be looking at these relationships that you know exist in the database and thinking well, surely SQL Server already knows how to join these two together. No, it doesn't. It wants you to tell it. So we have to say how are these tables joined and the way we do that is we use the word ON. And well they are joined is on the ProductCategoryID. The ProductCategoryID for a particular product should connect to the ProductCategoryID for the Category table.
So what I am going to type is ON a.ProductCategoryID = b.ProductCategoryID. We have now described how the join happens. I can execute that. What we get right now is exactly the same date as I got before, because I say that I'm joining this new table of ProductCategory, but not actually using anything from it, because what I want is the category name.
Now the category name in that table is just called Name. We already have a Name in our select statement but we need the second one. Well luckily we've already suffixed the table. So we just say I also want b.Name, and do notice that when I am using that b or a it knows which table I am talking about. So if I say a., it gives me all the columns from the Product table. If I say b., it's given me just the fewer columns from the ProductCategory table. So on b.Name, execute that one and now we're bringing back this information.
We are joining the two together and we're getting the names of the categories back. In fact because of this I could pretty much say I don't really care about ProductCategoryID anymore, so I could pull that off the SQL statement. Now officially what's happening here is we're doing what's called an inner join and if I was to be very correct about this, and I usually would be, I am actually going to use the words INNER JOIN instead of just JOIN here. What INNER JOIN means is we are only interested in those records where they match on both.
So if there was anything in ProductCategory that didn't have any products attached to it, we don't care. If there were any rows in Product that did not have a ProductCategoryID, we wouldn't care about those. We are only showing the rows where this information that matches in both tables. Again if I execute that, changing it to INNER JOIN, we get exactly the same results. There is also something called in OUTER JOIN. An OUTER JOIN means we are going to pick one of the tables and say well, we are interested in say all the Product Categories and all the matching products.
That sounds a little weird. So let me just demonstrate what the difference would be. Right now we are doing an INNER JOIN, which gives us 295 rows. I'm going to do what's called an OUTER JOIN, but I can't just write OUTER JOIN because it's going to get a little bit puzzled. With an OUTER JOIN you're always saying one of these tables takes presence over the other. We are always going to have everything that's in one. So we have to say am I interested in the second table, the one on the right, or am I interested in the first table, the one on the left of the JOIN statements.
And what I would actually say is either left OUTER JOIN or right OUTER JOIN. And what I am actually going to say here is I want to do a right OUTER JOIN on the ProductCategory table. Here is what the difference is. I execute that, bang! I actually have 299 rows now because we get four new rows and this is the difference, those first four. What's actually happening here is we're bringing back the names from the second table, the ProductCategory table, of Bikes, Components, Clothing, and Accessories, and it's actually telling us there is nothing in the Product table that is actually pointing directly to those categories.
But I want to list the categories anyway. Now the reason for this in this particular example is that this table has categories that reference each other, but there are no products that actually have the ProductCatagory Bikes, no products with Components, no product with Clothing or Accessories. And if we want them to show up, we do a right OUTER JOIN, giving precedence to the ProductCategory table. If we are only interested in the match, we say INNER JOIN and they will go away. Now there are a few other more unusual join things like CROSS JOIN. I'm not going to go through them in this course.
If you're interested again as with everything, you want to be having a good look at Books Online to see some further examples of where you can go with this. Do bear in mind that lynda.com also has a dedicated SQL course that gets into some of these join ideas as well.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.