Right Outer Join

Loading...

From the course by IBM

Databases and SQL for Data Science

505 ratings

IBM

Databases and SQL for Data Science

505 ratings

Course 5 of 9 in the Specialization IBM Data Science Professional Certificate

Much of the world's data resides in databases. SQL (or Structured Query Language) is a powerful language which is used for communicating with and extracting data from databases. A working knowledge of databases and SQL is a must if you want to become a data scientist. The purpose of this course is to introduce relational database concepts and help you learn and apply knowledge of the SQL language. It is also intended to get you started with performing SQL access in a data science environment. The emphasis in this course is on hands-on and practical learning . As such, you will work with real databases, real data science tools, and real-world datasets. You will create a database instance in the cloud. Through a series of hands-on labs you will practice building and running SQL queries. You will also learn how to access databases from Jupyter notebooks using SQL and Python. No prior knowledge of databases, SQL, Python, or programming is required. Anyone can audit this course at no-charge. If you choose to take this course and earn the Coursera course certificate, you can also earn an IBM digital badge upon successful completion of the course. LIMITED TIME OFFER: Subscription is only $39 USD per month for access to graded materials and a certificate.

From the lesson

Week 2 - Advanced SQL

By the end of this module you will: (1) earn how to use string patterns and ranges to search data, how to sort and group data in result sets, as well as (2) learn how to work with multiple tables in a relational database using join operations.

Join Overview3:08

Left Outer Join5:07

Right Outer Join4:04

Full Outer Join2:51

Meet the Instructors

Rav Ahuja
Data Science Program Manager
IBM

Hello and welcome to working with right outer join.

In this video, we will learn about creating a result set by joining rows from two tables.

At the end of this lesson,

you will be able to explain the syntax of

the right outer join operator and interpret the results set.

A join combines the rows from two or more tables based on

a relationship between certain columns in these tables.

There are two types of table joints;

inner join and outer join.

An outer join is a specialized form of join and there are three types of outer join;

left outer join, right outer join,

and full outer join.

This video explains right outer join or simply called right join.

Looking at the diagram,

the terms left and right refer to the table on

the left hand side and the right hand side of the diagram.

In this diagram, table 1 is the left table.

A right join matches the results from

two tables and displays all the rows from the right table

and combines the information with rows from

the left table that matched the criteria specified in the query.

In this diagram, the result set of a right join is all rows from both tables matching

the criteria specified in the query plus all non-matching rows from the right table.

Based on our simplified library database model,

if we want to check the status of all books out on loan,

this information is split between two tables.

The loan table which includes

the borrowers ID and loan date and the borrower table which includes the borrowers ID,

first name, and last name.

So, we need to identify the relationship between the two tables.

We do this by identifying the column at each table to link the tables.

In this example, we do this by matching the borrower ID.

Notice that the column borrower ID exists in

both the borrower table as the primary key and the loan table as the foreign key.

A primary key uniquely identifies each row in a table.

A foreign key is a set of columns referring to a primary key of another table.

In an outer join,

the first table specified in the from clause of the SQL statement is referred to

as the left table and the remaining table is referred to as the right table.

This is the syntax that the select statement for our right join.

In this example, the borrower table is the first table

specified in the from clause of the select statement.

So, the borrower table is the left table and the loan table is the right table.

Notice that in the from clause,

we identify the borrower table as B and the loan table as

L. This use of the letter B and the letter L is referred to as an alias.

In this join, each column name is prefixed with either the letter B

or L. This helps indicate which table each column is associated with.

Using an alias is much easier than prefixing each column name with the whole table name.

In the from clause, the loan table is listed on the right side of the join operator.

Therefore, we will select all rows from the loan table and combine this with

the contents of the borrower table based on the criteria specified in the query.

In this example, the criteria is the borrower ID column.

For a right join, we will select the following columns from the loan table,

borrower ID and loan date,

and we will also select the following columns from the borrower table;

borrower ID, last name, and country,

where the borrower ID in the loan table matches the borrower ID in the borrower table,

and here's the Result Set.

The Result Set shows each borrower ID

from the loan table and the loan date for that borrower,

where the borrower ID in the loan table also exists in the borrower table.

There are five rows in the Result Set.

Now, you can explain the syntax of

the right outer join operator and interpret the result set.

Thanks for watching this video.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2018 Coursera Inc. All rights reserved.

Download on the App Store

Get it on Google Play