Hey guys, I am Venkat and in this video we'll discuss, if we can replace a right join with a
left join and vice versa. We'll be using these two tables - "Genders" and "Employees". Here
is the sql script to create and populate them with test data. I'll have the script
available on my blog and include the link in the description box below this video. Now,
before we understand if we can replace a right join with the left join and vice versa, first,
let's understand what a right and left join would do? For that, let's write a query to retrieve
employee name from the Employees table and Gender from the Genders table for every employee. For
that, obviously we have to join these two tables and here's the query for that, select employee
name comma gender from Employees join Genders and then, we specify the join condition, on
Employees.GenderId = Genders.GenderId. Now, notice the join type that we have here, we just specified
join, "join or inner join" they're the same thing, what we have here is an inner join and we know
an inner join would return all the matching rows between both the tables and if we look at the rows
that we have in the Employees table, notice only these first two employees, Mark and Sara have a
GenderId. So, when we execute this inner join query, we'll only get Mark and Sara records,
so let's execute this query, there we go, as expected the matching two rows. So the important
point to keep in mind is, an inner join returns only the matching rows, left join on the other
hand returns all rows from the left table plus matching rows from the right table. Similarly,
a right join returns all rows from the right table plus matching rows from the left table.
At this point, you might be wondering why these tables called left and right tables? Well,
if you take a look at this query, by the way this is the same inner join query we just
executed. In this query as you can see, we're joining these two tables - Employees and Genders.
With respect to the JOIN keyword in this query, Employees table is present on the left hand side
of the join JOIN keyword and Genders table on the right hand side. So, in this specific query,
Employees is the left table and Genders is the right table. Now, let's execute all these three
queries. Notice, in the Genders table, we have three rows, in Employees also we have three rows,
but our join query only returned two rows, why? Well, that's because we're using an inner join
and inner join returns only the matching rows from both the tables and only Mark and Sara have
gender, so those are the only two matching rows, so the two employee rows are returned
with their respective gender. Now, let's use left join instead of inner join
Remember, a left join not only returns the matching rows from both the tables it also
returns all the non-matching rows from the left table. In our query the left table is Employees,
and in the Employees table, as you can see we've got three rows
and even in our result set we have all the three rows. Notice,
even Tom's record is returned. Tom's GenderId is null, so in the result set Gender column value
is also null. Now, let's change this join type to right join and see what's going to happen
Remember a right join not only returns the matching rows from both the tables,
it also returns all the non-matching rows from the right table. In our query, Genders is the
right table and in the Genders table, we have three rows. Notice, even in our result set we
have three rows including "Not Specified". Since we don't have any employee in the Employees table
with this "Not Specified" gender, EmployeeName column in the result set for "Not specified"
is null. Now, the question is, can we replace a right join with a left join and vice versa? Well,
we can and it's very easy to do. Let's actually look at it in action. First, let's make a copy of
this right join query, our goal is to convert this right join query into a left join query and still
produce the same result. So, first we change the join type from right to left and then we simply
change the table positions, so we make Genders the left table and Employees the right table.
These two queries should now produce the same result, there we go. So, the point is,
we can replace a right join with a left join and vice versa. For example,
just switch the table positions and use left join instead of right join and you get the same result,
even from performance standpoint there should be no difference. So, at this point you might be
wondering, then why do we have a right join when we can produce the same result using a left join
and vice versa? Well, it's sort of asking what do we need less than when we already have
greater than? Sometimes, depending on the output you want, especially when you are
joining three or more tables, it may be easier to use a combination of left and right joins
instead of only left or right joints. Like English, we read most languages from left to right
but there are languages that are read from right to left as well. So, depending on how you read,
some people may find left joins easier and others right joins, so it's purely
a matter of personal preference. I think, every query that uses a right join can be rewritten to
use a left join and the opposite is also true. If you can think of an example that doesn't
fit this pattern, please leave it as a comment. That's it in this video. Thank you for listening.