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 How to add Read More Read Less Button using JavaScript? #3 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #4 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
What does useEffect do in React?ReactObservation of Human Behavior [Shopping Observation Example]MiscellaneousThe simple difference between var, let and const in JavascriptJavaScriptHow to invalidate all existing cookies using Security Keys in WordPress?WordPressHow to create a cross-browser smooth scrolling with jQuery?jQueryHow to send and receive query strings via links in Angular 9?AngularHow to create HTML elements with ID and class using JavaScript?HTMLHow to create a pricing table using flex in CSS?CSSWhat are Null Sessions?MiscellaneousHow get_template_part helps write reusable code in WordPress?WordPressHow to select an element using its ID without the high specificity of the ID selector?CSSA complete guide to add responsive YouTube videos using HTML and CSSCSS