Category Archives: SQL

SQL joins

Now this is something that keeps falling out of my head. Took me a good deal of time to remember the simplest thing or left/right joins idea, and it’s just moments ago that I hard-nailed the fact that “inner” and “outer” are just help-words, a decor (here’s the equivalence table, thx this StackOverflow article):

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

 and now to fix the rest (thx another StackOverflow article). Note: if you're using MySQL, mind that it doesn't have full joins - although you can organize that as a UNION of left and right joins.

SQL join types

Also, while we’re at it – some normal forms memo:

1NF: each table field should contain only “atomic” values, i.e. single (not combined) values from  some finite domain – like, names. Domain might grow (new names appear), but each value (each name) should be an independent atom. Also each record, each occurrence or that field should contain a single value (of that domain) only

2NF: 1NF + table should not have duplicated records for a whole primary key. For instance, table with product, manufacturer, manufacturer_address is wrong – address would be repeated many times for no reason as it’s tied to manufacturer only (and the key is product+manufacturer). Should be two tables – manufacturer, address and product, manufacturer.

3NF: 2NF + all non-key attributes (fields) should be directly related to the key, and only to the key. For instance, if in the above (2NF) example table with product and manufacturer fields would also have product_factory and product_factory_address field – that would be wrong, because factory address is not related to the key (product+manufacturer) and should be located separately as product_factory, product_factory address table.

And I still fail to construct a proper phrase for what is “relational database”. This is bloody mess, all those descriptions that are there. I  get lost on the third word, no matter how many times I try. From what I can tell, it’s a database with a key->values relation, where the columns bear a “related” knowledge – unlike NoSQLs that have quite loose sense of what is hidden under the key (like, a user session under a hash key). Not sure it’s correct though =)