SQL Joins

Having spent most of my career working in environments where SQL development and ColdFusion development are handled by separate people or teams. The end result is that my SQL skills are not as evolved as my ColdFusion skills.

At my current job I am the one who writes the SQL for the small and medium-sized ColdFusion & SQL Server-based systems that I develop. Up until the other day I used implicit inner joins and over-used subqueries to get the data that I wanted from a database. My code worked, but it was ugly.

Ah Ha!

My SQL deficiency became apparent as I was sharing some code with a comrade yesterday. I was informed that my code had an “implicitly defined SQL Join”, and was informed that this style of coding is both deprecated and bad form. I was writing my queries like this…

SELECT	c.cheeseId,
	c.name,
	o.name AS 'odorName'
FROM	Cheeses c, Odors o
WHERE	c.odorId = o.odorId

…but as it turns out I should be writing them like this…

SELECT	c.cheeseId,
	c.name,
	o.name AS 'odorName'
FROM	Cheeses c
INNER JOIN
	Odors o ON c.odorId = o.odorId

Both queries will return the same results, but the latter is well formed, and consistent with other join types that you may have in your application.

Once I understood that, a lightbulb appeared over my head. I kicked out a few more code samples to observe the results of left and right joins (a concept that I have been having problems grasping).

Left Joins In The House

SELECT	c.cheeseId,
	c.name,
	o.name AS 'odorName'
FROM	Cheeses c
LEFT JOIN
	Odors o ON c.odorId = o.odorId

The above SQL will return all cheeses and their associated odors. Cheeses that don’t have an odor will also be returned with a null value in the odorName column.

Right Joins In The House

SELECT	c.cheeseId,
	c.name,
	o.name AS 'odorName'
FROM	Cheeses c
RIGHT JOIN
	Odors o ON c.odorId = o.odorId

The above SQL will return all odors and the cheeses that they relate to. Odors that don’t link to an associated cheese will also be returned with a null value in the cheeseId and name columns.

It’s interesting how not understanding one bit of theory or syntax can act as a closed door in your mind that keeps you from understanding a whole world of knowledge. This is why I always maintain an open mind and take a bit of time each day to read other developer’s blogs.