The Many Joins Of Mysql

[caption id=”attachment_545” align=”aligncenter” width=”1044”] Mysql joins[/caption]

Following the process of normalization - a well designed Relational database will usually have more than one table with related data in it. The reason for normalization is to separate data into tables, representing single entities, to prevent data redundancy. These tables will be linked together using foreign keys. When we want to correlate data from multiple tables we can use the join key word to do so. The following is a brief description of three commonly used joins: left, inner and right.

LEFT JOIN Will return all the rows in the table on the left of the join. It will then attempt to get any matching rows in the right table. If it can’t match a particular row, it will return NULL.

RIGHT JOIN This will do the opposite of the above, by returning all the rows in the table in the right of the join and then attempt to get matching rows from the table in the left. This join is less commonly used because you can simple swap the left and right tables in a left join to have the same effect.

INNER JOIN This join will return no NULL values. It will only return complete rows. That is, where a row in the left table matches a row in the right table. If there is a row in the left table but no matching row in the right table, the left row is not returned.

Example database tables

Dog

id	name	age	typeId	healthStatsId
1	Bengy	5	1	3
2	Maisie	9	4	2
3	Milly	2	4	12

Dog type

id	name	description
8	boxer	The Boxer is ..
2	poodle	The poodle is..
3	labrador	The labrador is..
4	cavalier	The Cavalier is..

Health

id	blood pressure	weight	height	length
3	60-90	65	57	82
12	65-90	55	50	70

Left join of tables Left join results

name	age	breed	description	blood pressure	weight	height	length
Bengy	5	null	null	60-90	65	57	82
Maisie	9	cavalier	The Cavalier..	null	null	null	null
Milly	2	cavalier	The Cavalier..	65-90	55	50	70

You will notice that in every row we have the Dog data. This is because the left join will return data from this table whether or not there is a match with rows in the table dog_type or the table health. For the top row “Bengy” - because it could not match any rows from dog_type it returns null values for those columns, next it did match a row from the health table and display the data.

Inner join of tables Inner join results

name	age	breed	description	blood pressure	weight	height	length
Milly	2	cavalier	The Cavalier..	65-90	55	50	70

In this case only one row was returned. This is because an inner join will only return matching rows, and because we have two inner joins in our query there must be a match in both right tables for the query to return a resulting row.