ELI5: Joins in Sql
SQL stands for the Structured Query language. It is a language used for managing RDBMS. RDBMS stands for Relational database management system. Keyword: Relational. It means that there will be structured data in form of tables and there will be defined relations, linking the data.
Linking data is crucial, there are many ways in which this can be done. But Joins are most probably the most used. Joins are actually simple. Once you understand how these operations work, you will find yourself using these more often.
Joins mean exactly what they mean in general English, they join tables based on a common parameter. For suppose if both the tables have a common column called ID, then these tables can be joined on ID.
There are four frequently used types of join - Inner join - Left join - Right join - Cross join
To better illustrate this, let us use two simple tables. One table with the user details and the second with the details of the boats they own.
Create table users
CREATE TABLE IF NOT EXISTS `users` ( `id` int(6) unsigned NOT NULL, `name` varchar(200) NOT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8; INSERT INTO `users` (`id`, `name`) VALUES ('1', 'a'), ('2', 'b');
Create table boats
CREATE TABLE IF NOT EXISTS `boats` ( `id` int(6) unsigned NOT NULL, `boat` varchar(200) NOT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8; INSERT INTO `boats` (`id`, `boat`) VALUES ('2', 'Titanic'), ('3', 'cruise');
Now, Table users contain
and table boats contain
Inner join returns the all the set of records which match in both users and boats. So, running inner join will have the following result
select users.id,boat from users inner join boats on users.id=boats.id;
and the result would be
Left join, as you would expect, will return a set of records which have a record on the left table, Now the result would be
select users.id,boat from users left join boats on users.id=boats.id;
Note that if it does not find a record associated with the right table, it just returns null.
Similarly, Right join will return a set of records which have a record on the right table.
select users.id,boat from users right join boats on users.id=boats.id;
Similar to left join, this also will just return null, if it does not find a record on the left table.
Cross join does a cross product and returns a result. That since users have 2 columns and boats has 2 columns. Cross join contains (2*2)=4 columns.
select users.id, boat from users cross join boats;
These are the most commonly used joins. Hope this post made it easier to understand joins.