MySQL. Links between tables. JOIN operator
In last lesson we created “sport” database and added three tables (countries, sports and teams). Furthermore we created links between tables with foreign keys. But we didn’t take into consideration one problem – how we can to delete or update data in these tables. In this lesson we’ll modify our database and make some queries simultaneously
If you want to update or delete data in one table, and this table links with another table, you need to write on update cascade on delete restrict while foreign key declaration. In this situation we mean, while update data in child table data in parent table will be update. But deleting restricts. Other variants are possible, for example, set null – while deleting in child table null value will be set in parent table.
Also we’ll consider inner join, left join and right join. Result will be the set of data from two tables.
All data from first table and data which satisfy condition from the second table will be in result set while left join. Also data with null value from the second table will be in result set