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

User · Answer

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

User · Answer

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

User · Answer

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    ---------- ----------- ---------

User · Answer

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

User · Answer

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

User · Answer

There are a couple of methods for full mysql FULL  OUTER  JOIN     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  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

User · Answer

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

[mysql] MySQL FULL JOIN?

Examples related to mysql