MySql join – the basics
By Peter
In a relational database with high level of normalization the data is stored in multiple tables. To retrieve the data from many tables joins can be used to connect data from multiple tables or itself(self join). Joins nowadays are considered to be a slow operation, and storage/memory is so cheap that de-normalization is one option in mostly NoSql and Graph databases. Joins can be slow as the database engine has to connect each record from one table to another record in another table with the columns we specify in each. The maximum is up to 61 tables(maximum join in MySql) but performance problems can occur even with a 5 table join. Good to keep in mind that if tables contain same column names in more then one table than identifiers are needed to prevent errors.
Types of joins in MySQL:
- Product or cartesian join
- Inner or comma join
- Outer join, left/right join
- Self Join
**Product join **
Cartesian join has no real-world usage only if testing for performance is the task. In this case if to tables are in the join with 4-4 records each than the ‘product’ is the result, all rows are matched with all others so the result is 16 rows.
**Inner Join **
Inner joins can be used if tables we want to connect have matching rows.
The following example is for the world database
This will bring results from country and countrylanguage tables, and connect them where code and countrycode is the same. This can be written also with a comma as
**Outer Join **
Outer join has two type left and right join, this refer to the way the table is joined to the other one, 1st table connected to 2nd or 2nd to 1st . A left join can give similar result as an inner join, but it can give the non matching records as result also.
Self Join
In some cases data from one table need to be shown, connected in one result set vertically. A logic of a self join works similarly as an inner join we just need to give an a alias to the table to identify it. A simple self join in the world database would like