The focus of this job currently is with SQL development. Some of the more complex SQL statements used here are easily 200+ lines long. Yesterday, I finally understood an enigma in my knowledge of SQL – the join.
Anytime that I referenced data from two or more tables, I simply “joined” them by setting the common field equal to each other in the WHERE clause. There is a much easier (and more controlled way) to do this. There are several join types that aide in linking tables together. You have a left outer, right outer, full outer, and inner join. The easiest way to differentiate between them is to illustrate a scenario:
A report needs to be generated of all skills that all employees have. One employee can have many skills, and many employees can have the same skill – a many to many relationship. Now, with a typical WHERE “join”, any employees that don’t have a skill are excluded from the result. Also, any skills not associated with an employee are excluded from the result. Inner joins drop results with a NULL partner.
Assume that the employee table is the first table (left), and the skills table is the second (right). By using a left outer join, all employees (even those without skills) are included in the result. By using a right outer join, all skills (even the ones not used by any employees) are included in the result. By using a full outer join, all employees and all skills both (regardless of the association being NULL) are included in the result. An inner join is the equivalent of using the WHERE technique, and can drop results that may be wanted.
I hope that this was easy enough that anyone can understand the concept now, as I spent many hours at many websites trying to understand when to use each type. Enjoy!
Zone of Mr. Frosti 