In this post, we will learn about SQL join operations. We can get the results by joining the two tables. If we want to join the two tables then we need to ensure both tables share a common column.

Basically there are 4 types of SQL join –

1. Inner Join
2. Left Outer Join
3. Right Outer Join
4. Full Outer Join

If you are still confused let’s see the usage of each join type with the help of an example.

Tables that we are going to use for example in this post –

Table – Employee

Table – Location

1. Inner Join

Inner joins are the most common type of join and also the most symmetrical because they require a match in each table that forms a part of the join. Rows that do not match are excluded.

Let’s say we have two tables Employee and Location. Employee table stores the details of all the employees and Location table stores the location details of each employee.

mysql> SELECT emp.eid, emp.fname, emp.lname, loc.location FROM employee emp INNER JOIN location loc ON emp.eid = loc.eid

Result

2. Left Outer Join

In the above example we saw that all the matching rows from both the tables are returned in the inner join. In the left outer join all the rows from the left table and the matched rows from the right table are returned. The rows in the right table which do not match with the left table, NULL will be returned.

mysql> SELECT emp.eid, emp.fname, emp.lname, loc.location FROM employee emp LEFT JOIN location loc ON emp.eid = loc.eid

Result

If you see in the location column, you will see NULL for eid 103

3. SQL – Right Outer Join

In the above example we saw that all the matching rows from both the tables are returned in the inner join. In the left outer join all the rows from the left table and the matched rows from the right table are returned. The rows in the right table which do not match with the left table, NULL will be returned for the left table.

mysql> SELECT emp.eid, emp.fname, emp.lname, loc.location FROM employee emp RIGHT JOIN location loc ON emp.eid = loc.eid

Result

Since there is no eid with 106 in employee table NULL values will be replaced for eid, fname, lname in the fetched results.

4. SQL – Full Outer Join

In Full outer join we get all the records from both left and right table. We can also consider Full outer join as a combination of left and right outer join.
If we do a full outer join between employee and location table that we saw above then below will be the result set returned.

mysql> SELECT fname, lname, location FROM employee emp FULL OUTER JOIN location loc ON emp.eid = loc.eid

Result