3

I got this question in one of the interviews asking about left and right joins in Mysql.

Why do we need Left join when there is already right and vice a versa?

Explanation:-

SELECT * FROM users LEFT JOIN profile ON (users.user_id= profile.user_id);

This will result is all matching data of profile table and all data of users table. Now what I do is do the same thing using RIGHT JOIN.

SELECT * FROM profile RIGHT JOIN users ON(profile.user_id = users.user_id)

This will also have the same result as of above query i.e what I did here is moved the left table to right and made the join from left to right table.

Now when I can perform this using left Join and also I can do the same using right Join. Is there any need to both the Joins or we can do everything using one JOIN only i.e either left or right.

I will be very thankful to get help of anyone who knows and can explain me the same.

Thanks in advance.

Chand Prakash
  • 196
  • 11
  • A combination of left and right joins results in a full outer join where there is no filtering... You should do some research. There's tons of questions about this already – John Ruddell Nov 30 '14 at 02:00
  • possible duplicate of [What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) – Tomas Creemers Nov 30 '14 at 02:03
  • 7
    That's not his question. His question is, why do we need a right join when the left join is enough all by itself. – Sebas Nov 30 '14 at 02:11
  • 3
    In complex queries with many tables involved, it's not always possible to reverse the order of the tables. Once a first joining pair is setup (ex: `select from table a join table b`) any subsequent addition (ex: `select from table a join table b left join table c`) is not free to choose the left/right operator. This operator depends on the existing join order. – Sebas Nov 30 '14 at 02:16
  • 3
    Why do you need `or` when you can do `not (not A and not B)`? Why do you need `COUNT()` when you can say `SUM(1)`? Why do you need `AVG()`, when you can say SUM()/COUNT()? Why do you need `CROSS JOIN` when you can say `ON 1=1`? As with most languages, SQL has lots of examples of redundancy. – Gordon Linoff Nov 30 '14 at 02:17
  • Hi Sebas, Thanks for the reply. I just want to know is there any query execution plan difference between left and Right Joins in mysql or if the query only the reason you have mentioned. Thanks for the reply. – Chand Prakash Nov 30 '14 at 03:19

1 Answers1

0

The reason why we need both right and left join is because both are tools for manipulating/analyzing data. The more tools, the better and easier.

Sometimes, depending on the project or the problem you can have a huge series of instructions which will be easier to understand/write using one or another instruction.

For example, try to rephrase this sentence using only RIGHT or LEFT

 SELECT (lotsOfFields ... ) FROM myTable
    INNER JOIN table1 on (moreFields)
    ...
    INNER JOIN table2 on (evenMoreFields)
    ...
    RIGHT JOIN table66 on (omgSoManyFields)
    ...
    LEFT JOIN table99 on (really?Fields)
    ...
    INNER JOIN table120 on (lulzFields)

Kinda useless if it already works like this, right?

The same happens with the > and < operators, the + and -, several logical operators, etc.

It's like asking why there are so many different kinds of knives. I guess it would be a bit hard to eat dinner with a machete, or to open path in the wild with a table knife.

El Gucs
  • 897
  • 9
  • 18