1

Customer 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
   FULL OUTER JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

According to TutorialsPoint, the output should be this:

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    1 | Ramesh   |   NULL | NULL                |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|    5 | Hardik   |   NULL | NULL                |
|    6 | Komal    |   NULL | NULL                |
|    7 | Muffy    |   NULL | NULL                |
|    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 |
+------+----------+--------+---------------------+

This is my real output instead:

╔════╦══════════╦════════╦═════════════════════════╗
║ ID ║   NAME   ║ AMOUNT ║          DATE           ║
╠════╬══════════╬════════╬═════════════════════════╣
║  1 ║ Ramesh   ║ NULL   ║ NULL                    ║
║  2 ║ Khilan   ║ 1560   ║ 2009-11-20 00:00:00.000 ║
║  3 ║ kaushik  ║ 1500   ║ 2009-10-08 00:00:00.000 ║
║  3 ║ kaushik  ║ 3000   ║ 2009-10-08 00:00:00.000 ║
║  4 ║ Chaitali ║ 2060   ║ 2008-05-20 00:00:00.000 ║
║  5 ║ Hardik   ║ NULL   ║ NULL                    ║
║  6 ║ Komal    ║ NULL   ║ NULL                    ║
║  7 ║ Muffy    ║ NULL   ║ NULL                    ║
╚════╩══════════╩════════╩═════════════════════════╝

So, I want to understand how the Full join exactly works as I'm not getting the same results as Tutorilaspoint site. I also want to know why the Full join query I posted above will have the exact same results as a LEFT JOIN.

Da black ninja
  • 359
  • 1
  • 6
  • 19
  • You would need to put in orders for customer ID not existing in the customer​ table to see any difference. See https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join/27458534#27458534 – Martin Smith Jun 04 '17 at 11:29
  • I think you are misreading/misinterpreting the TutorialsPoint link. Your output is what I would expect. – Tim Biegeleisen Jun 04 '17 at 11:30
  • The tutorialpoints example seems to be wrong. It makes no sense why it would duplicate some of the data. – James Z Jun 04 '17 at 11:37

1 Answers1

3

Your results are correct. Think of a FULL OUTER JOIN like this:

  • It returns all rows from an INNER JOIN on the two tables.
  • It returns all rows from the first table with no matches in the second.
  • It returns all rows from the second table with no matches in the first.

In your data example, all rows in the second table have matches in the first (that is, Customer_Id always matches a valid customer). Only the first two bullet points describe the result set.

I have no idea where their result set is coming from. My best guess is that it comes from the MySQL code. That code is not correct and does not approximate a FULL OUTER JOIN in any way.

Besides the obvious recommendation of finding a better tutorial, this is also a really bad example. In a properly created database, FULL OUTER JOIN is almost never necessary. The keys in one table should match the keys in the reference table. That is, you should never have a non-NULL Customer_Id that is not valid in the Customers table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786