SQL Join (Inner, Left Outer, Right Outer, Full Outer)

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
eid fname Iname
101 Ravikiran kada
102 Vikas kadam
103 Devendra joshi
104 Nilesh kumar
Table – Location
eid location
101 pune, Maharashtra
102 kottayam, kerala
106 Surat, Gujarat
104 Patna, Bihar

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

eid fname Iname location
101 Ravikiran Kada pune, Maharashtra
102 Vikas Kadam Kottayam, Kerala
104 Nilesh Kumar Patan, Bihar

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

eid fname Iname location
101 Ravikiran Kada Pune, Maharashtra
102 Vikas Kadam Kottayam, Kerala
103 Sevendra Joshi NULL
104 Nilesh Kumar Patna, Bihar

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

eid fname Iname location
101 Ravikiran Kada Pune, Maharashtra
102 Vikas kadam Kottayam, kerala
NULL NULL NULL Surat, Gujarat
104 Nilesh Kumar Patna, Bihar

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

fname Iname location
Ravikiran Kada Pune, maharashtra
Vikas Kadam kottayam, kerala
Devendara Joshi NULL
NULL NULL Surat, Gujarat
Nilesh Kumar Patna, Bihar

Leave a Reply