Mysql Left Vs. Right hand connection
I got this question in one of the interviews asking about left and right Mysql joins.
Why do we need a left connection when there is already a correct one and vice versa?
Explanation: -
SELECT * FROM users LEFT JOIN profile ON (users.user_id= profile.user_id);
This will cause the profile table data to match with all user table data. Now I am doing the same using RIGHT JOIN.
SELECT * FROM profile RIGHT JOIN users ON(profile.user_id = users.user_id)
This will also have the same result as in the previous request. What I've done here is move the left table to the right and make a left-to-right join.
Now that I can accomplish this using the left Join and also I can do the same using the right Join. Is there a need for both joins, or can we do everything using one JOIN just left or right.
I would be very grateful for the help of everyone who knows and can explain the same to me.
Thanks in advance.
source to share
The reason why we need both right and left join is because both are data manipulation / analysis tools. The more tools, the better and easier.
Sometimes, depending on the project or problem, you may have a huge series of instructions that are easier to understand / write using a particular 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)
Whatever is useless if it already works like that, right?
The same thing happens with the parameters> and <operators, + and -, multiple boolean operators, etc.
He likes to ask why there are so many different types of knives. I guess it would be to have a little lunch with a machete or open a path in the wild with a table knife.
source to share