|
Custom Search
| |
|
Using Outer Joins to Combine Data from Two Tables In last section about inner joins, we have seen that inner join can return data from two or more tables based on one or more join columns of common values. With outer join, we are able to retrieve data that have NO common values in the join columns. There are two types of outer joins - left join and right join, and total 4 variants of the left and right join.
The Practice Create two tables to demonstrate the JOIN concept and syntax. You eat pizza and/or pasta during the week. The queries below will work out:
(1) The days you eat pizza only. Pizza table: CREATE TABLE pizza (
Pizza table data: Pasta table: CREATE TABLE pasta (
Pasta table data: Practice #1: Return the days on which you eat pizza only or both pizza and pasta. In this query, Pizza table LEFT joins with Pasta table on common week days. It returns both matched and unmatched records. When matched, you eat both pizza and pasta on the day. If unmatched, you only eat pizza. Note that the unmatched rows are from the left table which is Pizza table. select t1.name, t1.food as food1, t2.food as food2
Query result set - 4 rows returned: Practice #2: Return the days on which you only eat pizza. In this query, Pizza table LEFT joins Pasta table on common week days. It returns only the unmatched records which are the days you eat pizza only. This is achieved by restricting to only NULL values in the Table2's Name column. select t1.name, t1.food as food1, t2.food as food2
Query result set - 2 rows returned: Practice #3: Return the days on which you eat pasta only or both pizza and pasta. In this query, Pizza table RIGHT joins Pasta table on common week days. It returns both matched and unmatched records. When matched, you eat both pasta and pizza on the day. If unmatched, you only eat pasta. Note that the unmatched rows are from the right table which is Pasta table. select t2.name, t2.food as food1, t1.food as food2
Query result set - 5 rows returned: Practice #4: Return the days on which you only eat pasta. In this query, Pizza table RIGHT joins Pasta table on common week days. It returns only the unmatched records which are the days you eat pasta only. This is achieved by restricting to only NULL values in Table1's Name column. select t2.name, t2.food as food2, t1.food as food1
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 |