SQL Joins

If you are interviewing for a web engineering position you are guaranteed to be asked to write an SQL join or two. This is an easy way for the interviewer to verify that you really do know how to write SQL and aren't lying about it. When I learned SQL back in the old days (early 90's) we didn't have fancy join statements. We did everything in the WHERE clause and we liked it. Seriously, I liked it. I had a hard time adjusting to reading and writing join statements. But over the last couple of years I've become accustomed to it. Let's look at a few different types of joins you can do. First let's define a couple of tables. Here's are your standard job interview test tables. NOTE: This article is written from a MySQL point of view.

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| employee_id   | int(11)      | NO   | PRI | NULL    | auto_increment | 
| department_id | int(11)      | NO   |     | NULL    |                | 
| name          | varchar(255) | NO   |     | NULL    |                | 
+---------------+--------------+------+-----+---------+----------------+

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| department_id | int(11)      | NO   | PRI | NULL    | auto_increment | 
| name          | varchar(255) | NO   |     | NULL    |                | 
+---------------+--------------+------+-----+---------+----------------+

I populated these tables with some random data. Most employees have corresponding records in the department table but some don't. Additionally, there are some departments without any associated employees.

Let's look at a few questions and answers to demonstrate the various types of joins you can perform on these two tables.

List all employees along with their department.

select
   e.name,
   d.name
from
   employee e
inner join
   department d using (department_id)

This uses an 'inner join' since the result set will only include rows from both tables where department_id matches. We could have omitted 'inner' and just used 'join'. The two are equivalent. However, I prefer to use 'inner join' since it explicitly states what is happening.

List all employees along with their department. Include employees who don't have a department.

select
   e.name,
   d.name
from
   employee e
left outer join
   department d using (department_id)

When performing a join the 'left' table is the table specified in the 'from' clause. To include all rows from this table whether or not there is a matching record in the department table you need to use a 'left outer join'. Technically you could just write 'left join' but again I prefer the more verbose syntax since it leaves no question about the intent of the query.

List all employees along with their department. Also include departments which don't have any employees.

select
   e.name,
   d.name
from
   employee e
right outer join
   department d using (department_id)

Using a 'right outer join' will force all rows from the department table to be represented in the result set. Right joins in a production environment are extremely rare. Personally, I've never seen or used one in any of the code I've worked on in my 13 years of experience. The concept is confusing and performance is poor. I strongly recommend against using them. But in case you're asked to explain a 'right join' in an interview you now know.

I'm not going to discuss the 'full outer join' since MySQL doesn't support it and like the 'right join' you shouldn't be using it anyway. But it does what you would expect it to. Include rows from both left and right tables regardless if there's a match or not.

Something else you shouldn't ever do in MySQL is substitute 'cross join' for 'inner join'. Seriously, in MySQL the two are syntactically equivalent which makes no sense since the phrase 'cross join' has always meant a cartesian product in my experience.