MySQL FULL JOIN?

Question

Following are my query and I want the result as given below. How can I do this in mysql ?

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set need to look like this:

LastName      FirstName     OrderNo
Hansen        Ola          22456
Hansen        Ola          24562
Pettersen     Kari         77895
Pettersen     Kari         44678
Svendson      Tove   
                           34764

Answer 1

MySQL lacks support for FULL OUTER JOIN.

So if you want to emulate a Full join on MySQL take a look here .

A commonly suggested workaround looks like this:

SELECT  t_13.value AS val13, t_17.value AS val17
FROM    t_13
LEFT JOIN
        t_17
ON      t_13.value = t_17.value
UNION ALL
SELECT  t_13.value AS val13, t_17.value AS val17
FROM    t_13
RIGHT JOIN
        t_17
ON      t_13.value = t_17.value
WHERE   t_13.value IS NULL
ORDER BY
        COALESCE(val13, val17)
LIMIT 30

Answer 2

There are a couple of methods for full mysql FULL [OUTER] JOIN.

1. UNION a left join and right join. UNION will remove duplicates by performing an ORDER BY operation. So depending on your data, it may not be performant.

    SELECT * FROM A 
      LEFT JOIN B ON A.key = B.key 
   
    UNION 
   
    SELECT * FROM A 
      RIGHT JOIN B ON A.key = B.key
   

2. UNION ALL a left join and right EXCLUDING join (that's the lower right figure in the diagram). UNION ALL will not remove duplicates. Sometimes this might be the behaviour that you want. You also want to use RIGHT EXCLUDING to avoid duplicating common records from selection A and selection B - i.e Left join has already included common records from selection B, lets not repeat that again with the right join.

    SELECT * FROM A 
      LEFT JOIN B ON A.key = B.key 
   
    UNION ALL
   
    SELECT * FROM A 
      RIGHT JOIN B ON A.key = B.key
      WHERE A.key IS NULL
   

Answer 3

SELECT  p.LastName, p.FirstName, o.OrderNo
FROM    persons AS p
LEFT JOIN
        orders AS o
ON      o.orderNo = p.p_id
UNION ALL
SELECT  NULL, NULL, orderNo
FROM    orders
WHERE   orderNo NOT IN
        (
        SELECT  p_id
        FROM    persons
        )

Answer 4

Try This:

(SELECT p.LastName, p.FirstName, o.OrderNo
 FROM   Persons p
   LEFT JOIN Orders o
   ON o.OrderNo = p.P_id
)
UNION
(SELECT p.LastName, p.FirstName, o.OrderNo
 FROM   Persons p
   RIGHT JOIN Orders o
   ON o.OrderNo = p.P_id
);

+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| Singh    | Shashi    |       1 |
| Yadav    | Sunil     |    NULL |
| Singh    | Satya     |    NULL |
| Jain     | Ankit     |    NULL |
| NULL     | NULL      |      11 |
| NULL     | NULL      |      12 |
| NULL     | NULL      |      13 |
+----------+-----------+---------+

Answer 5

Hm, combining LEFT and RIGHT JOIN with UNION could do this:

SELECT  p.LastName, p.FirstName, o.OrderNo
FROM    persons AS p
LEFT JOIN
        orders AS o
ON      p.P_Id = Orders.P_Id
UNION ALL
SELECT  p.LastName, p.FirstName, o.OrderNo
FROM    persons AS p 
RIGHT JOIN
        orders AS o
ON      p.P_Id = Orders.P_Id
WHERE   p.P_Id IS NULL

Answer 6

Full join in mysql :(left union right) or (right unoin left)

 SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    left JOIN Orders
    ON Persons.P_Id=Orders.P_Id
    ORDER BY Persons.LastName

    Union

    SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    Right JOIN Orders
    ON Persons.P_Id=Orders.P_Id
    ORDER BY Persons.LastName

Answer 7

Another alternative is to use a temporary table and the INSERT ON DUPLICATE KEY like this.

Creating Sample data:

CREATE TABLE A (
AKEY INT PRIMARY KEY,
AVALUE DOUBLE
);

INSERT INTO A VALUES(1, 0.1); 
INSERT INTO A VALUES(2, 0.2);

CREATE TABLE B (
BKEY INT PRIMARY KEY,
BVALUE DOUBLE
);

INSERT INTO B VALUES(2, 0.2);
INSERT INTO B VALUES(3, 0.3);

Table A data:

AKEY|AVALUE|
----+------+
   1|   0.1|
   2|   0.2|

Table B data:

BKEY|BVALUE|
----+------+
   2|   0.2|
   3|   0.3|

Creating temporary table:

CREATE TEMPORARY TABLE ABFULLJOIN (
ABKEY INT PRIMARY KEY,
AVALUE DOUBLE,
BVALUE DOUBLE
);

INSERT ON DUPLICATE KEY statement:

INSERT INTO ABFULLJOIN(ABKEY, AVALUE)
SELECT * FROM A;

INSERT INTO ABFULLJOIN(ABKEY, BVALUE)
SELECT * FROM B ON DUPLICATE KEY UPDATE BVALUE = B.BVALUE ;

FULL JOIN Query:

SELECT * FROM ABFULLJOIN;

Result:

ABKEY|AVALUE|BVALUE|
-----+------+------+
    1|   0.1|      |
    2|   0.2|   0.2|
    3|      |   0.3|

DROP temp table:

DROP TABLE ABFULLJOIN;

Answer 8

Everyone is correct here but writing same query twice is not a good way of programming ... so I have another way to do the full join in mysql which is as follows

SELECT
 user_id , user_name, user_department 
FROM 
 (SELECT 
     user_id  , user_name , NULL as user_department 
  FROM
   tb_users 
 UNION 
   SELECT 
     user_id ,NULL as user_name , user_department
   FROM 
      tb_departments
 ) as t group by user_id