How can I do a FULL OUTER JOIN in MySQL?

You don’t have full joins in MySQL, but you can sure emulate them.

For a code sample transcribed from this Stack Overflow question you have:

With two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

The query above works for special cases where a full outer join operation would not produce any duplicate rows. The query above depends on the UNION set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using an anti-join pattern for the second query, and then use a UNION ALL set operator to combine the two sets. In the more general case, where a full outer join would return duplicate rows, we can do this:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL

Related Posts:

  1. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  2. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  3. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  4. MySQL Multiple Joins in one query?
  5. Join vs. sub-query
  6. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  7. Unknown column in ‘field list’ error on MySQL Update query
  8. Unknown column in ‘field list’ error on MySQL Update query
  9. SQL Inner-join with 3 tables?
  10. MySQL – Operand should contain 1 column(s)
  11. MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050
  12. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  13. MySQL create table if not exists and insert record only if table was created
  14. How do I use properly CASE..WHEN in MySQL
  15. MySQL query String contains
  16. What’s the difference between VARCHAR and CHAR?
  17. Case statement in MySQL
  18. How to declare a variable in MySQL?
  19. MySQL Cannot Add Foreign Key Constraint
  20. How to split the name string in mysql?
  21. How do I import an SQL file using the command line in MySQL?
  22. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  23. mysql Foreign key constraint is incorrectly formed error
  24. MySQL: Invalid use of group function
  25. How can I return pivot table output in MySQL?
  26. Difference between JOIN and INNER JOIN
  27. Compare dates in MySQL
  28. MySQL “WITH” clause
  29. What is the difference between JOIN and UNION?
  30. Error Code: 2013. Lost connection to MySQL server during query
  31. MySQL syntax for Join Update
  32. SQL join on multiple columns in same tables
  33. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  34. how to drop partition without dropping data in MySQL?
  35. 1052: Column ‘id’ in field list is ambiguous
  36. 1052: Column ‘id’ in field list is ambiguous
  37. MySQL combine two columns into one column
  38. Simple way to calculate median with MySQL
  39. Error 1046 No database Selected, how to resolve?
  40. MySQL Error 1264: out of range value for column
  41. Remove duplicate rows in MySQL
  42. How to SUM and SUBTRACT using SQL?
  43. Cast from VARCHAR to INT – MySQL
  44. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  45. How do you force mysql LIKE to be case sensitive?
  46. MySQL – UPDATE multiple rows with different values in one query
  47. How to create a MySQL hierarchical recursive query?
  48. ERROR 1148: The used command is not allowed with this MySQL version
  49. Insert into a MySQL table or update if exists
  50. How to round down to nearest integer in MySQL?
  51. MySQL starts with searching issue
  52. Query to convert from datetime to date mysql
  53. Is it possible to GROUP BY multiple columns using MySQL?
  54. MySQL error: key specification without a key length
  55. Best way to test if a row exists in a MySQL table
  56. Check if MySQL table exists without using “select from” syntax?
  57. MySQL equivalent of DECODE function in Oracle
  58. mysql update column with value from another table
  59. The used SELECT statements have a different number of columns
  60. How to delete from multiple tables in MySQL?
  61. MySQL: Can’t create table (errno: 150)
  62. How to copy a row and insert in same table with a autoincrement field in MySQL?
  63. Alternative to except in MySQL
  64. Equivalent of explode() to work with strings in MySQL
  65. ROW_NUMBER() in MySQ
  66. MySQL OPTIMIZE all tables?
  67. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  68. MySQL – Get row number on select
  69. MySQL error: Unknown column in ‘where clause’
  70. How to truncate the text returned for a column in a MySQL query
  71. Using union and order by clause in mysql
  72. MySQL LIKE IN()?
  73. Conversion failed when converting date and/or time from character string while inserting datetime
  74. error, string or binary data would be truncated when trying to insert
  75. Cannot delete or update a parent row: a foreign key constraint fails
  76. What is the definition of cardinality in SQL
  77. must appear in the GROUP BY clause or be used in an aggregate function
  78. MySQL: Invalid use of group function
  79. Creating a search form in PHP to search a database?
  80. What does SQL clause “GROUP BY 1” mean?
  81. TSQL PIVOT MULTIPLE COLUMNS
  82. Microsoft OLE DB Provider for SQL Server error ‘80004005’
  83. Backup a single table with its data from a database in sql server 2008
  84. Is there a coalesce-like function in Excel?
  85. PHP Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/website/public_html/facebook/fbclogin.php on line 57 [duplicate]
  86. ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’
  87. How to retrieve sticky post in raw sql?
  88. Backticks (`) Instead of Single Quotes (‘) in an SQL Statement?
  89. SQL User Query by Multiple Roles using PHP
  90. $wpdb get_var issue
  91. Inserting rows into a custom table, when plugin is activated
  92. Posts modified in the last 48 hours
  93. post id not displaying
  94. How to run complex query using PHP
  95. Bulk find & replace on WordPress posts/pages (minus image paths)
  96. Improving performance of SQL sub-queries in WordPress
  97. Insert multiple checkbox values
  98. SQL Query to get post_id from wp_posts and and meta_key(s) from wp_postmeta
  99. SQL get last entry of a specific gravity form
  100. Deleting the MySQL database

Leave a Comment