Yogesh Chauhan's Blog

Is there a way to do a FULL OUTER JOIN in MySQL?

in SQL/MySQL on September 24, 2019

SQL FULL OUTER JOIN Keyword

As per the name the SQL FULL JOIN returns all the records from both of the tables. Its like combining 2 tables even with allowance of empty values in columns.

Let’s take a look at the syntax.


SELECT column/columns
FROM table_left FULL OUTER JOIN table_right
ON table_left.column= table_right.column WHERE condition;

NOTE: You can use FULL OUTER JOIN or FULL  JOIN, both are same.

Let’s take a look at the example and see the results. That will make more sense than just looking at the syntax.


SELECT c.CustomerName, o.OrderID
FROM Customers AS c
FULL OUTER JOIN Orders AS o ON c.CustomerID=o.CustomerID
ORDER BY c.CustomerName;

I have included example tables in DEMO but unfortunately MySQL doesn’t have FULL OUTER JOIN keyword. So there is no working demo available. However, we can emulate them in the following format to get the same results. 

Take a look at the following code:


SELECT * FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
UNION
SELECT * FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Take a look at the explanation given by MySQL here.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
Some Useful Operators in The SQL WHERE ClauseSQL/MySQLJavaScript arrays: a separate data type or Objects?JavaScriptHow to activate and deactivate hooks in a WordPress plugin?WordPressHow to add Date and Time picker in HTML and get the values using JavaScript?HTMLWordPress: How to query all posts from custom post type and display them in a list?WordPressHow to disable scrolling on html body on menu click using JavaScript?JavaScriptHow to convert a number rounding to a specified number of decimals in JavaScript?JavaScriptSQL GROUP BY StatementSQL/MySQLUnderstand Inheritance in PHP in this Basic Example (For Beginners)PHPDebugging in WordPress Part 1: WP_DEBUGWordPressWhat’s the difference between variables in CSS and SCSS (Sass)?CSSThe Lending Club Analysis using Logistic Regression and Random Forest in RStudioMiscellaneous