Now, you can see a little more clearly where the non-duplicated values are also coming back. AND P1.BusinessEntityID P2.BusinessEntityID One thing I want to do is show you if I was to comment out the non-equi join part, what the query would look like, and so what I’m going to do is just put P1 Entity ID just so we can see a little better what these columns are and run this. You can see where, for the first result, it brings in the entity ID where for PI, it’s 859, and for the second, for P2, it’s five, and this is for Gail Erickson, and you can see where for P2 or for P1, the middle name is null, and for the second copy, the middle name is A. We’ll bring back P1 Business Entity ID and then we’ll do P2 Business Entity ID. The first thing I’m going to do is add in the primary key so that you understand really what we’re bringing back here in terms of the values from each row. Let’s decompose this query so you can understand a little more what’s going on behind the scenes. If they matched, that means it’s the same record. We’re going to check to see if that primary key matches, because if they don’t match, then we know we have a duplicate, because if they matched, what would that mean? We’re going to be looking for matching Kris to Kris in one table and Wenzel to Wenzel in the other. We are going to join the person table on first name and last name. Rather than type this query out, I just paste it in, and what I want to show you here is that we are first joining from the person table and we are doing an inner join right back onto the person table. INNER JOIN Person.Person P2 ON P1.FirstName = P2.FirstNameĪND P1.BusinessEntityID P2.BusinessEntityID Let’s go look and see what that query looks like right now. There will be those cases where the business entity ID does not equal itself, and that is where non-equi join comes in, and that’s how we’re going to find duplicates. Naturally, we’re going to see where some rows are going to match right to themselves. If you can imagine, we’re going to start seeing where the table will start matching to itself. Given that and knowing that the first name and last name could potentially be duplicate, what we’ll do is we will join the table on first name and last name to itself, and that will start making combinations of rows. We know that the business entity ID is a primary key in the person table, so that is unique. What we’ll be doing is checking the table to see if there are any rows that have the same first name and last name across the table. We’ll be looking at the first name and last name. ![]() We’ll use the person table as our example. In this example, we’re going to check for duplicate values. Use Non-Equi Join to Check From Duplicate Values We’re also going to look at how you can match against a range of values, and then we’ll save the computing running totals example for the last lesson because that one’s a little more advanced. In this lesson, we’re going to go look at how you can check for duplicate data in a table. If it helps, both non-equi joins and inner joins are considered SQL inner joins!
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |