In this article, we will look into SQL Inner Joins through examples.
1. Introduction
SQL stands for Structured Query Language. It is a standard language for storing, manipulating, and retrieving data in databases.
2. SQL Join
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
There are four types of joins in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables. Make sure you only use this join when you know that the corresponding values exists in the joined tables otherwise no records will be returned.
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table. This type of join is used when you want to return the data from the left table even there is no mapping record in the right one. This is useful when you have optional values.
- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table. This type of join is used when you want to return the data from the right table event there is no mapping record in the left one. This is useful when you have optional values.
- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.
Let us look at a working example. We will take an example of an Employee. We will create three tables for this example – Employee, Department, Role. The employee table will contain information about the employee. The Department table will contain information about the various department in the company. The Role table will have information about the different types of roles. The Employee table has the foreign key reference to both the Department and Role table.
CREATE TABLE employee (
id int,
first_name varchar(255),
surname varchar(255),
department int, # Foreign key from the department table
role int); # Foreign key from the role table
CREATE TABLE department (
id int,
name varchar(255),
description varchar(255));
CREATE TABLE role (
id int,
name varchar(255));
Now let’s insert some data into these tables:
-- Insert into Department table
INSERT INTO department VALUES(1, 'IT Support', 'Handles daya to day IT issues');
INSERT INTO department VALUES(2, 'HR', 'Human Resource');
INSERT INTO department VALUES(3, 'Finance', 'Deals with finances');
-- Insert into Role table
INSERT INTO role VALUES(1, 'Developer');
INSERT INTO role VALUES(2, 'Programme Manager');
INSERT INTO role VALUES(3, 'Project Manager');
INSERT INTO role VALUES(4, 'Associate');
-- Insert into Employee table
INSERT INTO employee VALUES(1, 'Mark', 'Hay', 1, 1);
INSERT INTO employee VALUES(2, 'Tom', 'Cruise', 1, 2);
INSERT INTO employee VALUES(3, 'Amit', 'Bhandari', 2, 4);
Now let’s say that your manager wants to know the department and role of one of the employee – let say ‘Mark’. In this case, the join will come to use. We will join the three tables to get the information as below:
select * from employee e, department d, role r
where e.department = d.id
and e.role = r.id
and e.first_name = 'Mark';
Now let’s say there is an employee which had no role assigned yet:
INSERT INTO employee VALUES(4, 'Dan', 'Yas', 3);
Now let say we want to get the details of the employee. We will run a similar query as above but will use a different syntax.
select * from employee e
join department d on e.department = d.id
join role r on e.role = r.id
where e.first_name = 'Dan';
When we run this query we will not get any records back because we used the inner join.
ziameraj16=# select * from employee e
join department d on e.department = d.id
join role r on e.role = r.id
where e.first_name = 'Dan';
id | first_name | surname | department | role | id | name | description | id | name
----+------------+---------+------------+------+----+------+-------------+----+------
(0 rows)
We will need to join the role table with a left outer join to get the values back:
ziameraj16=# select * from employee e
join department d on e.department = d.id
left outer join role r on e.role = r.id
where e.first_name = 'Dan';
id | first_name | surname | department | role | id | name | description | id | name
----+------------+---------+------------+------+----+---------+---------------------+----+------
4 | Dan | Yas | 3 | | 3 | Finance | Deals with finances | |
(1 row)
3. Summary
In this article, we looked at different types of joins used in SQL. We discussed what these joins are and when they are useful. Then we looked at a working example of the joins discussed above. Joins are very useful and if you are working in a production-like environment that has a relational database the chances are that you will have to use them all the time.
