WEBVTT

00:00.000 --> 00:07.000
Advanced SQL joins, learning objectives.

00:07.000 --> 00:12.000
Understand and use different types of joins in SQL.

00:12.000 --> 00:18.000
Write queries using various join types to combine data from multiple tables.

00:18.000 --> 00:24.000
Understand how primary and foreign keys connect tables in relational databases.

00:24.000 --> 00:27.000
Concept of joins.

00:27.000 --> 00:32.000
In SQL, until now we've worked with data from single table.

00:32.000 --> 00:37.000
But in real databases, information is often spread across multiple tables,

00:37.000 --> 00:40.000
and that's where SQL joins come in.

00:40.000 --> 00:46.000
A join combines rows from two or more tables based on a related column.

00:46.000 --> 00:50.000
These related columns are usually a primary key and a foreign key,

00:50.000 --> 00:53.000
the backbone of relational databases.

00:53.000 --> 00:58.000
A primary key is a special column that uniquely identifies every record in a table.

00:58.000 --> 01:04.000
For example, in the students' table, the student ID column is the primary key.

01:04.000 --> 01:10.000
Because each student, Alice, Bob, Charlie, and David has a unique ID number.

01:10.000 --> 01:16.000
A foreign key, on the other hand, is a column in another table that refers back to primary key.

01:16.000 --> 01:20.000
In the enrollments table, the student ID column acts as a foreign key.

01:20.000 --> 01:24.000
Because it connects each enrollment record to a student in the students' table.

01:24.000 --> 01:29.000
And enrollment ID is the primary key in this table.

01:29.000 --> 01:31.000
Types of joins.

01:31.000 --> 01:34.000
There are four main types of SQL joins.

01:34.000 --> 01:36.000
Inner join.

01:36.000 --> 01:39.000
It returns only rows that match in both tables.

01:39.000 --> 01:45.000
For example, joining students and enrollments shows only those students who are enrolled in a course.

01:45.000 --> 01:48.000
As you can see, Charlie doesn't appear in the result.

01:48.000 --> 01:55.000
Because an inner join only includes records that have matching values in both tables.

01:55.000 --> 01:57.000
Left join.

01:57.000 --> 02:02.000
It returns all rows from the left table, even if there's no match in the right one.

02:02.000 --> 02:07.000
So you'll still see students like Charlie, even if they haven't enrolled in any course.

02:07.000 --> 02:09.000
Next is right join.

02:09.000 --> 02:14.000
It returns all rows from the right table and the matching ones from the left.

02:14.000 --> 02:18.000
So even though Charlie isn't enrolled in any course, he won't appear here.

02:18.000 --> 02:24.000
Only the courses from the enrollment table will, along with any matching students.

02:24.000 --> 02:30.000
Full outer join combines everything from both tables, showing all records whether they match or not.

02:30.000 --> 02:36.000
So here, you'll see all enrolled students, along with Charlie, who isn't enrolled in any course.

02:36.000 --> 02:40.000
Giving a complete picture of all your data.

02:40.000 --> 02:44.000
Each type of join helps us view data from different angles.

02:44.000 --> 02:52.000
Whether we want only matching records, all students, all courses, or everything combined for a complete overview.

02:52.000 --> 02:54.000
Comparing join types.

02:54.000 --> 02:59.000
Each join type serves a different purpose, and you can picture them like a Venn diagram.

02:59.000 --> 03:05.000
The overlapping area shows matched data, while the other parts show unmatched records.

03:05.000 --> 03:09.000
Use inner join when you only want the common data.

03:09.000 --> 03:12.000
That's the overlapping part of the Venn diagram.

03:12.000 --> 03:19.000
Use left join when you want all the data from the left circle, along with the matching records in the overlap.

03:19.000 --> 03:25.000
Use right join when you want all the data from the right circle plus the matches in the middle.

03:25.000 --> 03:32.000
And use full outer join when you want everything from both circles of the Venn diagram.

03:33.000 --> 03:41.000
To sum up, SQL joins let us connect related tables through shared keys, helping us view data as one unified source.

03:41.000 --> 03:50.000
Inner, left, right, and full outer joins each reveal unique relationships from exact matches to complete data sets.

03:50.000 --> 03:57.000
Mastering joins is key to turning scattered data into clear, meaningful insights across your database.