Geek Answers Handbook

Can we use the left join instead of the right join by placing table orders?

We are all familiar with the left association and the right associations. Can someone give one example where the desired result can be found only by a left join not by right (since we can change the position of the tables)? Actually I was asked this question in an interview.

+4
source share
3 answers

Yes we can.

The right and left outer joins are functionally equivalent. None of the functions provides any other function, so the right and left outer joins can replace each other as long as the table order is switched.

You can find the full explanation here.

A description of unions is also useful here . You can see the difference visually; -)

+2
source

Although there is no semantic difference ( table_a left join table_b gives the same result as table_b right join table_a ), there will almost certainly be a difference in performance.

The performance of right join usually worse, because with right join usually the left table is the "child" table of the right table, and the right table is the "parent" table, which is best suited for conditions, but indexed access is not possible because it is joined by a reverse foreign key. In addition, if your database is not corrupted, there will always be a row attached to the right table, which makes right join redundant.

Consider this query:

 select * from order_line right join customer_order where customer_order.date between $date1 and $date2

order_line rows order_line not have columns useful for searching - they are just children of the customer_order table, so this query will have to read every order_line row in the database and join the parent (and there should always be one), then apply the where where clause.

In a large database, this query will be so slow that it may never complete and probably exceed server resources unless there is an excessive exception of memory / disk usage.

This is probably why no one is using the right join and why I never saw it in any of the answers posted here on stackoverflow.

Remember: simply because the query returns the correct data, this does not mean that it is a good query (you can write bad code in any language).

0
source

More articles:


All Articles