Join Overview

Loading...

From the course by IBM

Databases and SQL for Data Science

79 ratings

IBM

Databases and SQL for Data Science

79 ratings

Course 4 of 4 in the Specialization Introduction to Data Science

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.

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 multiple tables.

In this video, we will learn about JOINing two tables to create a result set.

At the end of this lesson, you will be able to define the JOIN operator,

explain the role of primary keys and foreign keys in a JOIN operation,

and list different types of JOIN operators.

A simple select statement retrieves data from one or more columns from a single table.

The next level of complexity is retrieving data from two or more tables.

This leads to multiple possibilities of how the ResultSet is generated.

To combine data from two tables,

we use the JOIN operator.

A JOIN combines the rows from two or more tables

based on a relationship between certain columns in these tables.

Based on the book and author entity examples,

these are part of a simplified library database model.

This entity relationship diagram represents the relational data model for

the author and book entity as well as other entities such as borrower,

loan, copy, and author list.

In this model, the information is split into different tables.

If you wanted to know which borrower has which copy of a book out on loan,

you need to gather data from three tables;

the borrower, loan, and copy tables.

This is when you need to use the JOIN operator.

With the JOIN operator,

you are combining data from more than one table based on

a relationship between certain columns in these tables.

So, the first thing you need to do is identify the relationship between those tables.

That is, the column or columns in

each table that will be used as a link between the tables.

In this entity relationship diagram, notice the author_ID,

book_ID, borrower_ID, and copy_ID had the primary key icon.

A primary key uniquely identifies each row in a table.

Notice also the entities on the lower half of the screen,

some attributes have FK in brackets next to them.

For example, the copy entity has attribute book_ID with the FK in brackets.

This identifies the foreign key.

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

So, if you wanted to know which borrower has a book out or loan,

you need to gather data from the borrower and loan tables.

You will need the borrower ID from both tables.

SQL offers you several different types of JOINs.

It all depends on what you are looking for.

For example, you can extract a data set corresponding to

the intersection of the two tables involved or you can choose a bigger data set.

You can go up to the point of selecting

the combination of all the data from those two tables.

Now, you can define the JOIN operator,

explain the role of primary keys and foreign keys in a JOIN operation,

and list different types of JOIN operators.

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