|
Custom Search
| |
|
How to Simulate Full Join in MySQL - Part 2: return unmatched rows only In Part 1, we have looked at how to simulate Full Join to return all the records from both the left and right tables. In this tutorial, we'll study how to simulate Full Join to return only unmatched records from both the left and right tables. The following full join diagram illustrates only section A and C are returned in the result of the full join.
We'll re-use our Pizza and Pasta example used in Outer join tutorial. If MySQL supported Full Join, the correct full join syntax to get section A and C would be as below. -- Note: the following syntax does not work in MySQL Note the difference here to Part 1 is that the syntax in Part 1 does not have the clause for WHERE condition. Because MySQL does not support Full Join, we'll have to work out section A and C separately, then vertically append them together by using UNION or 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 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, t2.food as food2, t1.food as food1
Query result set - 3 rows returned:
Append vertically (A + C) by using UNION or UNION ALL UNION or UNION ALL can be used and they make no differences because section A and C are mutually exclusive. select t1.name, t1.food as food1, t2.food as food2
Query result set - 5 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 |