Saturday, July 31, 2021

Difference between LEFT and RIGHT OUTER Joins in SQL - MySQL Join example

There are two kinds of OUTER joins in SQL, LEFT OUTER join and RIGHT OUTER join. The main difference between RIGHT OUTER joins and LEFT OUTER join, as their name suggests, is the inclusion of non-matched rows. Sine INNER join only include matching rows, where the value of the joining column is the same, in the final result set, but OUTER join extends that functionality and also include unmatched rows in the final result. LEFT outer join includes unmatched rows from the table written on the left of the join predicate. 

On the other hand, RIGHT OUTER join, along with all matching rows, includes unmatched rows from the right side of the table.  In short, the result of a LEFT outer join is INNER JOIN + unmatched rows from the LEFT table and RIGHT OUTER join is INNER JOIN + unmatched rows from the right-hand side table.

Similar to the difference between INNER join and OUTER join, the difference between LEFT and RIGHT OUTER JOIN can be better understood by a simple example, which we will see in the next section.

By the way, joins are very popular in SQL interviews, and along with classic questions about finding the second highest salary of an employee, Inner join vs outer join or left outer join vs right outer join is commonly asked.




LEFT and RIGHT OUTER Join Example in SQL

LEFT vs RIGHT OUTER JOIN in SQL, MySQL databaseIn order to understand the difference between LEFT and RIGHT outer join, we will use once again use classical Employee and Department relationship. In this example, both of these tables are connected using dept_id, which means both have the same set of data in that column, let's see data on these two tables.

mysql> select * from employee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|    103 | Jack     |       1 |   1400 |
|    104 | John     |       2 |   1450 |
|    108 | Alan     |       3 |   1150 |
|    107 | Ram      |    NULL |    600 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | Sales     |
|       2 | Finance   |
|       3 | Accounts  |
|       4 | Marketing |
+---------+-----------+
4 rows in set (0.00 sec)

If you look closely, there is one row in the employee table which contains NULL, for which there is no entry in department table. Similarly, the department table contains a department (row) Marketing,for which there is no employee in the employee table.

Difference between LEFT and RIGHT OUTER Joins in SQL


When we do a LEFT or RIGHT outer join it includes unmatched rows from the left or right table. In this case, LEFT OUTER JOIN should include employees with NULL as department and RIGHT OUTER JOIN should include Marketing department. Here is an example of LEFT and RIGHT OUTER Join in MySQL database :

mysql> select e.emp_id, e.emp_name, d.dept_name from employee e LEFT JOIN department d on e.dept_id=d.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|    103 | Jack     | Sales     |
|    104 | John     | Finance   |
|    108 | Alan     | Accounts  |
|    107 | Ram      | NULL      |
+--------+----------+-----------+
4 rows in set (0.01 sec)

As I said unmatched rows, i.e. row with dept_id as NULL has been included in the final result and dept_name for that row is NULL, as there is no corresponding row for NULL dept_id in department table. 

But note that Marketing department is not included in this result. Now, let's see an example of RIGHT OUTER JOIN in MySQL, this should include Marketing department but leave out employee with NULL dept_id.

mysql> select e.emp_id, e.emp_name, d.dept_name from employee e RIGHT JOIN department d on e.dept_id=d.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|    103 | Jack     | Sales     |
|    104 | John     | Finance   |
|    108 | Alan     | Accounts  |
|   NULL | NULL     | Marketing |
+--------+----------+-----------+
4 rows in set (0.00 sec)

As I said, the final result set has Marketing department and emp_id, emp_name is NULL in that row because there is no employee with dept_id=4 in the employee table.

Difference between LEFT and RIGHT OUTER JOIN in SQL

In short, the following are some notable differences between LEFT and RIGHT outer join in SQL :

1) LEFT OUTER join includes unmatched rows from the left table while RIGHT OUTER join includes unmatched rows from the right side of the table.

2) The result of the LEFT OUTER join can be seen as INNER JOIN + unmatched rows of left able while the result of the RIGHT OUTER join is equal to INNER JOIN + unmatched rows from the right side table.

3) In ANSI SQL, the left outer join is written as LEFT JOIN while the right outer join is written as RIGHT JOIN in select SQL statements.

4) In Transact-SQL syntax left outer join is written as *= and the right outer join is written as =*, Sybase database supports both syntaxes and you can write join queries in both ANSI and T-SQL syntax.

That's all on the difference between LEFT and RIGHT OUTER JOIN in SQL. We have seen an example of RIGHT and LEFT join in MySQL database but since we have used ANSI syntax of OUTER joins, it's for other databases like Oracle, Sybase, SQL Server, and PostgreSQL as well.

JOIN is one of the most important and common concepts in SQL and you should be good enough to figure out which rows will be included as a result of the JOIN statement before actually running that SELECT query against any table. Sometimes erroneous JOIN queries can bring loads of data and potentially may hang your database so beware of it.

9 comments :

Virat said...

Joins adds lot of complexity in query, I prefer to use subquery instead of Join, they seems to me more natural.

Anonymous said...

@PravinShirke:
I was asked this question in one of my inteviews 5-6yrs back and i have still not able to find the answer, maybe you can answer it hopefully..w.r.t above classic example.
Q. If we perform left outer join on employee and department it will return all records from left table and only matching from right. similarly, right outer join will return all the records from department and only matching from employee table. NOW... what if we swap the tables in left outer join. i.e if we perform left outer join of department and employee which will return same records as output as right outer join. THEN THE QUESTION IS WHY DO WE HAVE RIGHT OUTER JOIN IN SQL???

Anonymous said...

"THEN THE QUESTION IS WHY DO WE HAVE RIGHT OUTER JOIN IN SQL???"

Anything you can write as a "RIGHT OUTER" could be written as a "LEFT OUTER" by just flipping the tables, so technically "RIGHT OUTER" isn't functionally required in order to have working code.

It was created to allow the coder to express it that way if it makes more sense to them that way. So basically it exists for readability and potential maintainability.

Having said that I rarely see anyone code a "RIGHT OUTER", most people just automatically order their tables to be "LEFT OUTER" joins if an outer is needed.

Anonymous said...

What is difference between LEFT OUTER Join and INNER JOIN SQL? and Why it's so important?

javin paul said...

Main difference between LEFT OUTER Join and INNER Join is that former will also include non-matching rows from left table, while INNER join will only include matching rows. To better understand, consider classic Employee and Department schema joined together by DEPT_ID. How do you list ALL departments with number of employees there? If you use INNER join you will miss out empty departments because those DEPT_ID will not be found in DEPARTMENT table. This is the classical mistake SQL programmers make, not just on interviews but also while writing queries.

Unknown said...

Quoting Virat :

"Joins adds lot of complexity in query, I prefer to use subquery instead of Join, they seems to me more natural. "

You my friend should not be allowed to write SQL. And I really do mean that.

Anonymous said...

Quoting Anonymous:
"Having said that I rarely see anyone code a "RIGHT OUTER", most people just automatically order their tables to be "LEFT OUTER" joins if an outer is needed."
I agree. I don't know the ANSI SQL standard well enough to know if there actually IS a theoretical difference between LEFT and RIGHT. Nevertheless Microsoft Access QBE GUI will create a RIGHT OUTER JOIN if you want to list all tuples from the detail table (m-side) and only matching tuples from the master table (1-side). At least it will stay quiet if you manually change the RIGHT into a LEFT OUTER JOIN flipping the tables' names. I don't know any example (yet) where you are actually forced to use RIGHT OUTER joins.

Anonymous said...

It's so simple it's for getting fully records from right side table and also get the records which is similar to left side table

Anonymous said...

THEN THE QUESTION IS WHY DO WE HAVE RIGHT OUTER JOIN IN SQL??? I will say not all people read from left to right as some read right to left. Therefore; the natural flow for that group would be the right outer join and not the left outer join. My other crack at that is just simply left can't be without right.

Post a Comment