Hi, Jim Hudson – oaktreeit.com. Today we’re going to take a look at the — mostly the conceptual difference between an inner and a left outer join. Anytime you’re working with any kind of data, especially relational data, you’re going to see the concept of a join.
Whether you’re a report writer or a developer or a SQL DBA, at some point, you’re going to need to join tables because SQL Servers are relational — whether it’s Microsoft SQL Server or Oracle’s SQL Server product, you’re going to relate tables. And one of the — I want to say “basic skill sets”, but that’s really true because this is not necessarily a basic skill.
One of the journeyman skill sets — one of the common or necessary skill sets necessary for a report writer or a DBA or a developer — is just the ability to join tables. There are lots of different join types. There’s left outer and right outer and full outer and cross joins and inner joins, but the two I see most of the time are inner joins and outer joins. In fact, most of the time, it’s going to be an inner join or a left outer join. And one of the great things about SQL is because it’s a standard, then the code that you’re looking at will work on both SQL server and Oracle SQL Servers — excuse me, Microsoft SQL Servers and Oracle SQL Servers.
This first line — I don’t want to spend a whole lot of time on full outer joins, but the whole idea of using the full outer join here, which is useful, is that a full outer join is going to give me all of the data from both tables — and that’s kind of what I wanted to see here. I wanted to see all of the data. So, if we run both of these queries, then here at the top, I can see all of the data in table A and all of the data in table B. And we can see right here in the “on” clause, we’re using a key — we’re using this key to join the data. And, of course, this is incredibly simple and again, it’s designed to help you understand the concepts. So, basically, the two types of joins that we want to look at the most today are inner joins and left outer joins. An inner join, it just doesn’t get any simpler. An inner join returns rows that match, ok? And in this data right here, these are the rows that match. So, if I have, you know, conceptually, customers and orders and I do an inner join, then I’m going to get all customers who have orders. What I’m not going to get is perhaps any new customers that haven’t ordered with us yet. So, if I want all of a particular table, then I could — for example — I could do a left outer join and that’s going to give me — a left outer join is going to give me everything in the left table, but only what matches in the right and nulls where they don’t match up. And we can see this down here — I’m getting everything on the left table. The table A only has 1, 2, and 3. Table B, as far as matching, only has 2 and 3, but it doesn’t have a match for row 1, therefore, we get a null. So, here’s a simple, easy thing to remember: inner joins give me rows that match, left outer joins give me everything in the left table, and only what matches in the right. And always be sure to join your tables on good keys.
Jim Hudson, oaktreeit.com.