|
Custom Search
| |
|
How to Simulate Full Join in MySQL - Part 1: return both matched and unmatched rows Full Join returns all the records from both the left and right tables for either matched or unmatched rows. Please note the following guidelines when using Full Join.
In the Full Join diagram below, we can see three sections (A, B, C) form the result of a full join.
We'll re-use our Pizza and Pasta example from our Outer join tutorial. If MySQL supported Full Join, the correct full join syntax to get section A, B, and C into the result set would be as below. -- Note: the following syntax does not work in MySQL Because MySQL does not support Full Join, we'll have to work out section A, B, and C separately, then vertically append them together by using UNION ALL. Section A Section A contains rows that are found only in Table1. Query below achieves this effect by using LEFT join with WHERE clause specifying that a column in Table2 is null. select t1.name, t1.food as food1, t2.food as food2
Query result set - 2 rows returned:
Section B Section B contains rows that are found in both Table1 and Table2. Query below achieves this effect by using INNER join. select t1.name, t1.food as food1, t2.food as food2
Query result set - 2 rows returned:
Section C Section C contains rows that are found only in Table2. Query below achieves this effect by using RIGHT join with WHERE clause specifying that a column in Table1 is null. select t2.name, t1.food as food1, t2.food as food2
Query result set - 3 rows returned:
Append vertically (A + B + C) by using UNION ALL UNION ALL is used in the last step of Full Join simulation. We have used Union ALL because FULL JOIN keeps duplicate rows in the result if any, this is by definition of SQL FULL JOIN, not a side-effect. select t1.name, t1.food as food1, t2.food as food2
Query result set - 7 rows returned:
Happy Coding!
Other tutorials in this category 1. Using Inner Joins to Combine Data from Two Tables |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |