Bharat Kalluri     ·  About

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

id name
1 a
2 b

and table boats contain

id boat
2 Titanic
3 cruise

Inner Join

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

id boat
2 Titanic

Left join

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;
id boat
1 (null)
2 Titanic

Note that if it does not find a record associated with the right table, it just returns null.

Right Join

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;
id boat
2 Titanic
(null) cruise

Similar to left join, this also will just return null, if it does not find a record on the left table.

Cross join

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;
id boat
1 Titanic
2 Titanic
1 Cruise
2 Cruise

These are the most commonly used joins. Hope this post made it easier to understand joins.

Cheers!

Written February 23, 2018.

← Pipenv is awesome!  Docker for developement →