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