Editorials

Basic SQL Joins

One of my favorite things to ask during an interview for a developer who is going to be working with databases is to enumerate the different kinds of joins that are standard in most SQL engines. Not only naming what they are, but how the work, or better yet, how they may be utilized is a great follow on series of questions. So, I might ask a question such as this:

What are the different kinds of JOINS that are supported in the SQL language? How do they work? Can you think of a scenario where you would use it? So, in case you are going to interview with me, this is something I would be looking for.

The Inner join is a method of gathering data from two tables. All records from table a are returned for every matching record in table b.

The left outer join returns all records in table a (left) along with every matching record in table b (right). If no record exists in table b, a record from table a is returned with null values for all of the non-matched data in table b.

The right outer join is the inverse of the left outer join. Instead records are returned from table b (Right) with matching or null values from table a (left).

The cross join is a join from two or more tables without join criteria. It returns all records from table a (left) joined to all records in table b (right) regardless of the contents.

This is the basic list of joins that may be found in almost any SQL engine. They are the foundation on which we build our relational database in order to maintain data integrity, reduce redundancy of data, and improve performance by saving data values the least number of times. If you don’t have a handle on these five joins, it’s time to get studying. It doesn’t matter if you are not wanting to be a database professional. Any person working with persistence needs this basic understanding in order to be proficient when working in software.

I’ll leave the rest of the exercise to you. See if you can think of scenarios where the different kinds of joins make sense.

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail
  • Eilenblogger

    You named 4 join types then write that you should have a reference on these 5 joins.

  • Tommy Petersen

    Ben, you forgot to describe the full outer join. You obviously had it in mind as you write 5 types, but only describe 4. My least favorite is the right outer join, people have a hard time thinking that way.