Enrol to start learning
Reading is open to everyone. Enrolling is free, and it is what unlocks the audio lessons, practice tests and progress tracking.
2.3. RIGHT JOIN / FULL OUTER JOIN
Learn content
Interactive Audio Lesson
Unlock the classroom podcast
The transcript is free to read. A free account plays the conversation back.
Today, we're going to explore RIGHT JOIN. Can anyone tell me what we might use RIGHT JOIN for in data analysis?
Maybe to show all records from a table even if there's no corresponding data in another?
Exactly! RIGHT JOIN allows us to retrieve all data from the right table, even if there are no matches in the left. For example, if we want to see all orders regardless of whether they have customer data.
Could you give an example of the SQL syntax for that?
Sure! A RIGHT JOIN syntax looks like this: SELECT customers.name, orders.order_id FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id; This gets all orders, including those without associated customers.
What about cases when we need to see unmatched records?
Great question! This is where FULL OUTER JOIN comes in. Let's discuss it next.
To summarize, RIGHT JOIN shows all entries from the right table, which is useful when we're focused on that table's data.
Unlock the classroom podcast
The transcript is free to read. A free account plays the conversation back.
Now, let's talk about FULL OUTER JOIN. Can anyone explain what makes this join special?
It combines the results of both LEFT JOIN and RIGHT JOIN, right?
Correct! FULL OUTER JOIN gives us all records from both tables. It helps when we want to identify records that don't have corresponding entries in the other table.
Could you show us the syntax for that?
Absolutely! Here’s how it looks: SELECT * FROM customers FULL OUTER JOIN orders ON customers.id = orders.customer_id;. This retrieves all customers and orders, filling in gaps with NULLs where values are missing.
When would we use FULL OUTER JOIN over just one of the other joins?
When analysis requires visibility into all records regardless of matches. It’s especially useful for auditing datasets. When checking for missing data, FULL OUTER JOIN is invaluable.
In summary, remember that FULL OUTER JOIN includes everything from both tables, marking unmatched rows with NULLs.
Overview
Short Summary
This section introduces RIGHT JOIN and FULL OUTER JOIN in SQL, explaining their purpose and how they differ from other join types.
Medium Summary
RIGHT JOIN and FULL OUTER JOIN offer unique ways to retrieve data from multiple tables in SQL, allowing analysts to combine data even when some records do not match. This section outlines their syntax and provides examples to highlight their utility in data analysis.
Detailed Summary
In SQL, joins are crucial for combining data from multiple tables based on related fields. RIGHT JOIN retrieves all records from the right table and matches records from the left table where possible. This is useful when the analyst is more interested in data contained in the right table, even if there are no corresponding entries in the left. FULL OUTER JOIN, on the other hand, combines results from both LEFT JOIN and RIGHT JOIN, returning all records from both tables. If there are unmatched records, NULL values are included where data is missing from either side. Understanding these joins expands a Business Analyst's ability to analyze complete datasets and perform comprehensive data validation in their reports and dashboards.
Audio Book
Unlock the audio lesson
The script is above and free to read. A free account plays it back, in the voice you pick.
Create a free accountRIGHT JOIN / FULL OUTER JOIN Used less frequently by BAs, but useful to find unmatched records.
Detailed Explanation
RIGHT JOIN and FULL OUTER JOIN are types of SQL joins that are designed to help analysts work with data from multiple tables. A RIGHT JOIN returns all records from the right table and the matched records from the left table. If there is no match, NULL values are included for columns from the left table. On the other hand, a FULL OUTER JOIN returns all records when there is a match in either left or right table records. For unmatched records, the result will have NULL values for non-matching columns from both tables.
Examples & Analogies
Imagine you have two lists: one of all your friends and another of party invitations sent out. Using a RIGHT JOIN is like checking all the invitations sent out and seeing who RSVP'd. If someone didn't, you'd still see their name with a blank next to it to indicate no response. A FULL OUTER JOIN is like combining both lists to see who is invited and who didn't respond, so you can see all your friends alongside the invitations, regardless of whether they participated.
Unlock the audio lesson
The script is above and free to read. A free account plays it back, in the voice you pick.
Create a free accountRIGHT JOIN Returns all records from the right table, even if there are no matches in the left.
Detailed Explanation
A RIGHT JOIN ensures that all entries from the right table are displayed, regardless of whether there is a corresponding entry in the left table. This is particularly useful in scenarios where you want to keep track of all records from the right table, such as sales data, while checking against another table for supplementary information. Each record from the right table will be shown, and any missing data from the left table will be filled with NULLs.
Examples & Analogies
Think of a scenario where a school has a complete list of students (the left table) and another list of extracurricular activities (the right table). If you want to ensure that all activities are listed, even those that no student signed up for, you would use a RIGHT JOIN to include all activities, showing NULL for students who did not participate.
Unlock the audio lesson
The script is above and free to read. A free account plays it back, in the voice you pick.
Create a free accountFULL OUTER JOIN Returns all records when there is a match in either left or right table records.
Detailed Explanation
FULL OUTER JOIN combines the results of both LEFT JOIN and RIGHT JOIN, allowing for a complete view of the data from both tables. If there are matches, it shows them; if there are no matches, it still displays all rows from both tables, filling in with NULLs where necessary. This type of join is valuable when you need a comprehensive overview of two datasets, ensuring that no information is lost.
Examples & Analogies
Imagine you are comparing two inventories: one of fruits available in your local market (the left table) and another of fruits sent by the supplier (the right table). A FULL OUTER JOIN would allow you to see a complete list of both: every fruit in the market and every fruit sent, showing which items might have been ordered but not yet delivered, or which ones are in the market but not ordered at all.
--
Key concepts
Examples
Step-by-step examples to apply the section's ideas and test your understanding.
Example of RIGHT JOIN: SELECT customers.name, orders.order_id FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;
Example of FULL OUTER JOIN: SELECT * FROM customers FULL OUTER JOIN orders ON customers.id = orders.customer_id;
Memory aids
Imagine a party with two groups: one from the left and another from the right. RIGHT JOIN shows everyone from the right group, even if they don't mingle with the left group.