If the table names are reversed in Left join will it result in Right join?

Admin 1
Viewed 653

Consider the following situation . We have two tables users and orders. User table has three Columns Id,Name, Order_id . Orders table has two columns Id, and order_item.

SQL query to get all the orders for each user.

 select * from users left join orders on users.order_id = orders.id;

SQL Query to get all users for each order

 select * from users right join orders on users.order_id = orders.id;

But I'm using Sqlite3 which currently does't not support Right, Full Outer join. So I tried following left join with inversed table names. which kinda solve my problem.

 select * from orders righ join users on users.order_id = orders.id;

So my question is if I reversed the tables name in left join will it result in Right join ? Of course Right join has prints table columns in different order my query prints in different.

Please help me with this approach ? comparison with Real Right outer join , disadvantages and other notable things ?

sqlite sql
2 Answers

If there are only two tables, then yes.

With more tables, things get a little complicated. The queries are not exactly the same, but they are under most circumstances. The equivalent to:

from a left join b left join c

is:

from c right join (b right join a)

The issue is the order in which the joins are interpreted. So the first version is:

from (a left join b) left join c

and this is subtly different from:

from a left join (b left join c)

depending on the conditions.

user_11440350

RIGHT joins are rarely used.
The reason is that a LEFT join is easier to perceive for most people (my opinion, although maybe people that use RTL writing may disagree).

When you want all the rows of Table1 and any matching rows of Table2, usually you think about a resultset which at the left side of the results has the columns of Table1 and in the right side the columns of Table2.

So you write:

SELECT * FROM Table1 LEFT JOIN Table2 ON <condition>

This is exactly the same as:

SELECT * FROM Table2 RIGHT JOIN Table1 ON <condition>

Of course Right join has prints table columns in different order my query prints in different

You must set explicitly the order of the columns in the results.
So instead of SELECT * if you want first the columns of Table2 write:

SELECT Table2.*, Table1.* ....

or if you don't want all the columns, enumerate all the columns that you want:

SELECT Table2.col1, Table2.col2, ...., Table1.col1, ....

Of course it's a good practice to use aliases to make the code shorter, so your query can be written:

SELECT o.*, u.* 
FROM orders AS o LEFT JOIN users AS u
ON u.order_id = o.id;
user_104988280
Related