CODEWITHSUNDEEP
mysqljoin
Robert
Robert

Reputation: 10390

Right Outer Join from tutorial

I was reading about the right outer join from tutorialspoint. I know that when a right outer join is performed the first thing that happens is an inner join of the two tables and then any rows in the right table that are missing in the left table are given null values.

Example from tutorial:

Customers table:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Orders table:

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Query: 

SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Result:

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

Why is the result of the right outer join the same as the original right table? How is this right join in anyway useful? I see it as pointless.

Upvotes: 1

Views: 48

Answers (2)

Barmar
Barmar

Reputation: 781068

This isn't a useful query for RIGHT JOIN. Since all orders should have a valid customer (in fact, there should be a foreign key relationship between the Order and Customers tables), there will never be an order with no matching customer, so you'll never get any null values added.

I think they included this query just to contrast it with the almost identical query on the LEFT JOIN and FULL JOIN pages of the tutorial. Those queries show all orders, as well as all customers that don't have any orders (they have NULL in the AMOUNT and DATE columns.

To get the equivalent result with RIGHT JOIN you can simply swap the order of the tables:

SELECT  ID, NAME, AMOUNT, DATE
FROM ORDERS
RIGHT JOIN CUSTOMERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Because LEFT JOIN and RIGHT JOIN are equivalent like this, most programmers just use LEFT JOIN.

Upvotes: 2

0xF
0xF

Reputation: 586

This is not the right example in that case.To see something which is there in orders table but not in customers table the id should be something that is not present in customers,such as 8,9 etc.

Upvotes: 0

Related Questions