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
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.
select * from firstTable, secondTable;
Inner joins can be used if tables we want to connect have matching rows.
The following example is for the world database
select name as 'Country Name', language as 'Language Spoken' from country inner join countrylanguage where code = countrycode;
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
select name as 'Country Name', language as 'Language Spoken' from country, countrylanguage where code = countrycode;
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.
select name as 'Country Name', language as 'Language Spoken' from country left join countrylanguage on code = countrycode where language is null;
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
select t1.indepyear, t1.name, t2.name from country as t1, inner join country as t2 where t1.indepyear = t2.indepyear and t1.name = 'Qatar';