All language subtitles for Joins

Body in progress.

Okay.

Can we start in today's session?

We are going to learn about the concept called cardinality.

Are relationships between tables.

Captain and their relationship between tables.

Let us consider.

We have two tables.

Table one and table two.

When it comes to these relationships in SQL, we have three types of relationships.

There are 1 to 1.

One too many.

One.

Many too many.

Many.

Too many.

Minute.

To me, these three are the relationships which we have in SQL.

So when it comes to first thing, 1 to 1 relationship.

One value in table one will be matched with only one value in table.

In both table one and table two.

Only one value should be matched.

More than one value is not the image.

One value in table one should be matched with one value in table two.

Cabinet match with more than one value.

For example, we have two tables called countries table.

And Prime Minister Stephen.

Country stable and prime minister stable in country stable.

You are storing the list of countries with their ID number or something.

The serial number or something you are storing.

List of countries in prime ministers table You are storing list of prime ministers for each country.

How many prime ministers will be there?

One One value in country will be matched with one value in Prime Minister's table.

One value in country stable will be matched with only one value in prime Minister.

Stable.

The.

We will have country names, country ID, any related value.

Primary key and foreign key value like Prime Minister ID.

After that, we have something called one to many relationship.

One to many.

One value in table one will be matched with many values in table.

One value in table one will be matched with many values in table two.

For example.

Department and employees.

No.

For stable, you will have only department name, department, ID and all.

In the second table, you will have employee details.

Under one department, we can have more than one employee in each department.

We can have more than one employee.

One value in department table will be matched with many values in.

Employee status.

When it comes to this 1 to 1 relationship and one to many relationship, these two can be achieved only

with two tables.

If we have table one and table two, we can get these relationships.

For example, let me show you small tables.

We have three countries.

Who is the prime minister of India?

America.

Pakistan.

Google.

Google.

Think.

Got something we can transfer from person.

Okay, here we have countries table.

We will have country ID.

Just one, two, one, three.

For each country after that name.

After that name.

Here you will have Prime Minister.

Name.

Name?

And I'll show you how.

Country aid.

This country is foreign key here.

This country.

ID is primary key here and it is foreign key in prime minister table.

We will have primary stages one, two, three and Countrywide also.

One, two, three.

We will match with these two tables, we will have primary key and foreign key relationship.

Right.

How many values in first table is matching with values in second table?

Only one value in first table is getting matched with one value in second table.

One value in first table is in getting matched with one value in second value.

This is called 1 to 1 relationship.

We will not have more than one value in the second value or more than one matching value in first,

first or second table.

If you have more than one matching value.

In any tables, we will call it as one too many.

One too many.

For example.

But no, nothing like that.

One value in table one will be matched with one value in table.

Yeah, we can have a number of records also.

Even if you have 1000 values.

Only one value in table one will be matched with one value in table.

Let us see.

About one too many.

We thought that department.

UN employees.

Department.

Department name.

Okay.

I have two columns department ID and department name.

I have four departments.

A department.

Mighty operations.

Since.

Like that.

I have some departments.

For each department.

Will there be only one employee?

We will have more than one employee right here in employee table have employee.

Employee name.

And department ID.

And department employee ID employee name and department.

These are the employees.

And that department is.

Yeah.

In our department, there are two people.

Budget Department.

There are two people.

One value in department table is getting matched with multiple values in the employee table.

But.

Based on the department.

ID relate these two tables.

How many people are working in.

So how many people are working in IT department?

How many people are working in operations?

One.

How many people are working in sales department?

One value in table one is getting matched with multiple values in table.

One value in table one is getting matched with multiple values in table.

Huh?

Yeah, we will have relationship with multiple venues.

That's all it is called as one to many.

More than one column is.

Yeah, we will have only one related column between both the tables.

Primary key value and foreign key value.

By using these values, we will relate each other.

Here, if we have one value is matching with multiple values in another table.

It is called one to many relationship.

One value is matching with one value in another table.

It is called 1 to 1.

If it is matching with multiple values.

It is called one to many.

We will have one more relationship called many to many.

Many.

Too many.

Many.

Too many.

Air for writing this many to many relationship.

We will have one more table, which is called bridge table.

At least we need three minimum tables to write, many to many relations.

For example, I have trainers and students.

Reign of Unstolen.

A teacher and student.

One teacher will have multiple students write.

Once one teacher will have multiple students and one student also will have multiple teachers.

Only one teacher will not teach all the subjects.

One student will have multiple teachers.

Here.

Some decide it will be many from table one and table two.

Both sides.

It will be many to many.

For example, I have four trainers.

Trainer one.

Train up to.

Reign of three and reign of four.

And here I have ten students.

There is one student who.

Student three.

Like this.

I have two different tables.

How to build a many to many relationship between these two tables.

This is trainer ID one, two, three, four.

This is student ID.

This is Trainer Stable, which contains trainer and trainer name.

This is Prudent table which contains student ID and student name.

Now how to build.

Many to many relationship between them.

But.

Personal.

Well, here I have only two tables called trainer and student.

We can't have one more column here.

One to many relationship.

For example here if I have trainer id as.

Foreign key.

For one student, there will be multiple trainers.

For example.

Student one is getting trained, trained from trainer one and two.

How can you have multiple values here?

We can't have like the.

We cannot have multiple values like the.

For example.

Student two is getting trained on four subjects.

Is getting trained under four trainers.

His trainer should be one, two, three and four.

How can we build like this?

It is not possible.

Is it possible to build like this?

It is not possible.

That's why we need the help of one more table called bridge table.

In both trainer table and students table will have only primary key.

Primary key values.

In both trainers table.

On student table, I will have only primary key values.

Foreign key values will be present In one more table is called student trainers table, which is bridge

table.

I will build one more table with the name of student trainers table.

Here I will have.

Student ID and trainer ID.

We just.

Which is foreign key value from both the student ID is foreign key of student table and trainer ID is

foreign key of trainer table.

What I will do here.

Student ID one.

Back to training on the train ride.

Number.

Student number one.

Training under trainer number two.

Student ID number one Took training under Trainer three.

Student ID Number two Training under Trainer one.

Student ID number two Took training Under.

Brainer number two.

Student two Took training under.

Trainer Number three.

Student Number three Training under.

Trainer number four.

By using this bridge table as mediator.

We can relate one to many relationship, one to many relationship, many values.

In student table.

Many values in student table is getting matched with many values in trainers table.

Without this bridge table, we can't build a many to many relationship.

Without this bridge table, we can't build it.

Many to many relationship.

Why do we need to learn about these relationships?

For the topic called James.

For the topic called James.

Only if you know how these relationships are working, then only you can do joins.

The purpose of using joints is.

To retrieve data from multiple tables.

Joint, not joint.

To retrieve data.

From multiple tables.

They will use the concept called joins to retrieve data from multiple tables.

But why do we need to retrieve data from multiple tables?

Actually, at the beginning, without knowing about normalization, we have data only in single table,

every single big table to save our storage, we divided the data into multiple tables.

What if I want the data together?

I want to print the data together.

I am not storing here.

I just want to print the data of both the tables.

I should know how these both tables are related with each other.

About these two tables are related with each other, and only by using that relationships.

I can join both the tables.

By joining both the tables, I can retrieve data from multiple tables.

For example, in our scenario, I want all the student details along with their course details in a

single table.

Just want to print it in the output.

Stored data in multiple tables to save the storage.

But I want to print in single table.

How can we do it?

By using concept called joints we can do.

For example, here in our database.

We created student table and course details table.

Right.

We have one negative point here, actually, for course, on student tables, we should have many to

many relationships.

Many to many relationship to not to go to complex.

I just used one too many.

So let.

Select star from student.

First, you have to choose your database.

Select star from student details.

Here.

I have details of multiple students.

Here.

I have details of multiple courses.

In our scenario, one student cannot enroll for multiple courses.

Check properly.

One student cannot enroll for multiple courses.

Under one course, there will be multiple students.

One student cannot enroll for multiple courses because it is one too many which we use.

We build only two tables.

Right?

If we want to build many to many, we need to take one bridge table.

And we should have foreign key values in the bridge table.

Here.

Check this course.

Details have two courses.

Protect student details Table.

One student enrolled for only one course.

What if this student ID number one want to enroll for one more course?

The student ID one enroll for now if you want to enroll for power also.

What?

Like it is not possible here.

The student ID is primary.

You can't duplicate it.

Student primary can't duplicate it.

You want many to many relationship in both the tables.

We should take two tables which contain only primary key.

You have to store course details separately and student details separately.

After that, we should have one more bridge table with course student details our student course details.

Where we will have only student ID and course ID Both are foreign keys.

We should take the help of the bridge table and we can get many to many relationship.

Yeah, we can work on multiple tables.

Over the two weeks of no vegetable will have only foreign keys.

Both of parent tables are to course details and student details will have only primary key.

The.

Okay.

Yeah.

Unpredictable.

It.

How?

Each galaxy.

It.

Trainer.

ID Trainer.

Length.

Hair.

Student ID.

Student Name.

Here we have student ID as foreign key.

We will relate it with student ID number.

Trainer id as foreign key.

We will relate it with trainer ID and we will get the.

If we will have one more item, One more foreign key.

Foreign key can have duplicate values.

If we have one more.

Table called course details.

Here you have coarse and coarse name.

Here you have one more thing called.

Courtside.

And you will write one, two, three, whatever the course required because one trainer can take multiple

courses.

Right?

One trainer can teach multiple courses.

And there will be multiple trainers for each subject.

You want to find it out?

Course and course name also.

You can write this course.

For example, I have four side, one for side two our.

I have only two courses and I have four trainings.

Trainer one is teaching SQL.

A trainer too is teaching power by.

Again, this trainer three is sorry.

Raynor three is teaching school.

In.

No.

By using this particular table, we are relating all the three values.

He student ID number one.

Got trained from trainer number one.

Got trained on yesterday.

No, we need not to insert values.

Only if a student is enrolling for the course.

We will insert his values.

For example, if.

Student four is there.

He enrolled only for one course.

Student ID number for.

Enrolled for.

I enrolled under trainer ID number one, and he enrolled only for his details.

His details are completed.

Only one record for him.

Is the best no brainer.

Who will teach them.

It's.

Yeah, of course did.

Only foreign key values.

Yeah, it will carry only the values and it will relate multiple tables.

This particular table is also called as fact table.

This table is also called as fact Table.

This primary.

Key value tables are also called as dimension tables.

Dimension table.

But even if we have ten dimension tables, it will be giant.

It will be related with one fact table.

But.

Shelton.

Active.

In joints.

We have two types, basically.

There are inner joints and outer.

Inner joins and outer joins.

Inner joins and outer joins.

What is this inner join is this inner join will give only the matching values between the positive.

It will print only matching values.

In both attorns.

I will show you how it works.

One minute.

In course.

Details Table.

All of you inserts one more value.

Which course number three.

Outside.

Number three, tableaux until something.

Site number three.

You can take anything.

Python.

A double.

Whatever you want, you can take.

18,000.

Originality is nothing but of the relationships between the.

No, it is not a command.

It's just the concept.

I inserted one more value into course details.

Here.

I have four students in student detail still.

I have four students in student retention.

Course, 81.

What is course side number one for side number one again for side number two, power.

This for side number one and two are getting matched in both the tables.

Do we have any matching value for course, number three here?

No, we don't have any matching.

If we want to print only the matching values, then we have to use the join called inner join.

It will not print the details of Python.

Will not print the details of Python.

If you want the details of both matching values, plus unmatched values, then you have to use the join

called outer join.

If you want to get the details of both matching values.

Plus unmatched values.

Unnaturalness.

Matching renders plus unmatchable.

If you want to print both the details, then you have to use the single outer joins.

In the inner joints.

We have only one keyword called join.

Giant women use only giant.

Giant is the keyword for.

The syntax for writing join is.

Let me show you.

First you have to write what are the columns that you want to print?

Select.

I want to print student ID.

Name.

Yes.

Gender.

Pourseyedi.

Location.

Yeah, of course.

Let me print course name.

Course name.

See.

On that last location.

Student.

Name.

Age.

Gender.

Course.

Course.

Name.

Fee and location.

This data I have in two different tables.

I have this data in two different tables.

The student details are present in student details.

This course, name and fee are present in course retention.

Yes, sir.

We have this data in two different tables.

Yeah.

Some student details.

Select.

And after that, select the column names from student details.

Giant watch details.

Am joining the both the tables.

And you have to give condition based on which value you are joining both the tables.

What is the related value in these two tables?

What is the related value?

Related column in both the tables.

Pourseyedi.

Foresight side is the foreign key value here, which is used to build relationships between both the

tables.

It.

I am taking alias name for student details table.

I am taking student details as SB.

On first details ACB.

After that, you have to give condition on.

On on is the condition value.

It will print the matching value.

It will check the matching value with this on condition is used to check the matching values between

both the tables on student details.

Dot.

Dot for side is equal to.

Course.

Details, Dot course.

Student details that course equal to course details that course ID.

Am joining the both the tables based on this particular value.

I'm joining both the tables based on this particular value called.

Waterside by matching course sides in both the tables.

Am joining the data which is presented two tables.

This is how we can print data from multiple tables.

This is how we can print data from multiple tables.

If I execute this particular query, I will get error.

Will get there.

Will get error which is called poor side is ambiguous.

Course it is ambiguous while writing joins.

You should remember about this particular error.

Definitely.

Because this error will confuse you.

Most of the times query is completely correct, but you will get this error.

Most of the times if I execute this particular query column for side in fields list is ambiguous.

What is this particular service?

Ambigious.

Why only course it is ambiguous.

Why not another column?

So I took course completely.

Yet this particular column for this column is.

This course column is present in both the tables.

Ambiguous means confusing?

Yes, it is feeling confusing that from which table?

I want to print this course.

This course is present in course Details table and also in student details.

Table.

From which table have to print this course.

You can give any table.

Even if you give student student details table name, it will print course properly.

Even if you give course details table name, it will print properly.

As you didn't mention any table names here.

Is throwing the error called course in fields, which is ambiguous.

Remaining columns are okay because they are present in only one table.

Only this course ID column is present in both the tables.

That's why while writing joins what we have to do for each and every column, we have to mention table

name also.

A street art student at networks for student details.

Table two dot Student Dot Name.

Yesterday that age.

Alias name took here by using as student details as.

It's easy for all the columns present in student details we can use.

Easy.

For this course column, you can give either course details, table or student table.

Is do not proceed.

This course name is printing course details fee is present in course details and location is present

in student details.

We have to mention table name.

Column name.

Yeah, if you can name, you can give any alias.

But instead of writing student details or course course details.

Course writing will become more lengthy.

I use just aliases.

So.

I print the data of both the tables together.

Student ID one enrolled for fee 15,000 in Bangalore location.

Shut up.

This is written in short form.

This is shortest query.

In.

I wrote in multiple lengths for readability.

You can write in single line.

Select.

And after select, you have to mention all the column names from both the tables which you want to select.

From table one.

Join table two on table one.

Dot Primary key is equal to table two dot foreign key.

We have to give that linker column which is generated, which is acting as a relationship between both

the tables.

All of you write this particular query.

Okay.

And.

But when we observed to wit.

Yeah.

Okay.

Mm.

It's showing.

Version one point.

The current balance is not changing.

It is not changing automatically.

It will not change.

We didn't wrote any query to change it, right?

For that, we have to write one more additional query.

The query will be.

Okay.

We told we already discussed that this inner join will give only the matching values.

This inner giant will give only the matching values.

Now I want the details of Python.

We have to use outer joints.

We have to use outer joints.

In outer joints.

We have three types of joints which are called left joint.

Left the giant.

Right.

Join.

Unfreezing.

Left to join, Right, join and full join.

In outer joints.

We have three types of joints which are called.

Left.

Join, right, join and full join.

What is the purpose of this left?

It is left to join will give all matching values from both the tables.

Which means the output of inner giant.

Now, Jane is giving matching values between both the tables.

Right.

It will give all matching values between both the tables, plus unmatched values from left table, unmatched

values from left table.

What is left table and what is right table?

You can ask me.

We are writing table one.

Giant able to.

This is the syntax actual syntax to join here also in normal use student details join course details.

The student details is student details is in left side.

We write it in single line.

Student details will come.

Table one, which is in left side for the join and table two is in the right side, which is course

details.

This will become right table.

If we have it, will print all the matching values and if we have any unmatched value from table one,

it will print that unmatched value also.

When it comes to right join, it will print all the matching values plus unmatched value from right

table, which means it will print only the matching values and unmatched values only from the right

side table.

In our example it is course details table.

Do we have any unmatched value in course details?

Yes, we have value of Python left table.

We don't have any unmatched values, but in right table we have Python as unmatched value.

We'll print the details of Python also.

Full join.

This full join will print all the matching values plus unmatched values from both the tables.

From both left and right tables.

It will print all the matching values and unmatched.

Values from both left and right tables.

Look.

No, we are just printing.

We are not storing the data by using giants.

We can just print.

For example, in the same way, no need to change any syntax.

Instead of this join.

Let me Right.

Left join.

Do we have any match values in the left table?

Do we have any unmatched values?

Student Details?

No.

It will print same this inner join output.

It will print the same inner join output.

I use love to join.

It will print same in that journal.

Same.

We don't have.

Left to join.

If you want unmatched value from the left table we have to use.

Left join.

If you want unmatched value from the right side table, what you have to do.

A matching value from right side table.

We have to use, right?

Join.

Why do we have any unmatched value in course?

Details Table.

Yes, we have Python as unmatched.

Now it will print the details of Python also.

All the remaining values will be null.

It will print all the matching values an unmatched value from right table.

If we have any unmet value from the right side table, it will print that value out.

What is Fujian?

It will print all the matching values and unmatched values from both the tables.

Which is what I am doing if I use full join.

Losing it will throw me.

A SQL syntax error.

He is throwing red color into work.

Even if I execute it will throw me have an error in SQL syntax corresponding.

His full joint.

We don't have any keyword called full join industrial.

We don't have any keyword called full join in SQL.

That's why if you want to get the output of full join, what you have to do is you have to write left,

join.

Left to join and remove the semicolon after the query.

After that, right?

Union.

Union.

At the end.

Rachel.

Right.

We have to add both left and output and right join.

Yeah.

When it comes to Fulton, we don't have any keyword called Fulton.

If you want the output of full join, you have to write left, Join union, right, join or right,

join union, Left.

Join.

Anything.

I exude this will get all the matching numbers on my matching numbers.

Oh.

Left joint Union.

Right.

Join or right.

Join Union Left.

No, no.

First we have to write Leftjoin query completely after that union.

After that, write Giant query Complete.

But left the join query union right joint query which is completely single query.

Angelidis this left joint union.

Right.

First we have to know the logic, right?

At least to write one particular thing.

We know logic from next thing we can copy paste.

By tomorrow or after tomorrow.

Sessions We will complete basics.

All basics.

These concepts will come under SQL basics.

So is.

This started.

I will not take text.

I will conduct class.

Next Saturday there will be test on all the syntaxes we have to write queries.

Not the theory.

On festivals.

We have to study more.

We have to pray God and we have to study.

That's.

These are the inner and outer joins.

We have one more type of joins.

No more giants that the Giants will come.

These two are very easy.

One thing I will explain to you, one thing I will give you as a task.

Try by tomorrow if you don't get the output.

We can solve it tomorrow.

After that, we have something called miscellaneous joints.

No one named them left and right joint.

No one named them.

That's why we have miscellaneous journals which are called Cross Joint.

Cross join.

Wonderful stuff.

Ross Self Self-join.

This cross joint is also called as Cartesian product.

Cartesian product.

What is the purpose of using this cross?

Join this cross.

Join will give all the combination between both the parents.

It will give all combinations between both the tables.

All combinations between both tables.

For example, we have four students.

I have four students and am providing three courses.

These are the three courses I'm providing.

I have four students and I am providing three courses.

All combinations means it will take all.

It will give all the combinations between both the tables will.

I'm choosing this one condition.

I'm choosing power is one combination.

I'm choosing Python is one combination.

Some are choosing is one combination.

Some are choosing one combination, some are choosing.

Python is one combination.

John Choosing school is one combination.

John Choosing power is one combination.

John Choosing Python is one combination.

Peter Choosing SQL is one combination.

Peter Choosing power is one combination.

Peter Choosing Python is one combination.

These are all the possible combinations between these two tables.

How many possible combinations are there?

Completely.

For all.

For each person.

There are three possible subjects.

Four people are there.

Total total combinations.

Oh.

Well, yeah.

Total to all comers.

What you said is correct.

How some person told other.

Why it is called Cartesian product is in the output.

You will get the product of number of records present in table one into number of records present in

table two.

Here I have four records, details of four people here.

I have three courses, records of three courses, four into three.

You will get total records in the output.

You will get to all records in the output will give all the possible combinations between both the tables.

We'll give all possible combinations between both the tables.

Syntax is very easy.

Syntax is very easy.

Select.

Star from.

Student details.

Ramaphosa is.

Select star from Student Details Campus News.

Yeah.

If I execute this particular query.

And yet course details.

Spending.

Rom choosing python.

Rom Choosing power.

Rom Choosing SQL.

After that.

Peter Choosing Python.

Peter Choosing power being.

Like that for each student, you will get all the possible combinations.

Mm.

What?

Mm.

Known for writing, for getting all the combinations you have to write Select Star from table one,

comma table.

We'll give cross join.

Okay.

Now we have most important interview question which is also.

Project to to get all the combination values.

For example, if you are making any portal business.

Purtell because you are creating some database for Portal.

You have to give all combinations for food items.

For example, you have for breakfast.

Improving.

The mathematician.

You are taking, you are having for breakfast and you are having 6 or 7 types of curries.

Like that.

We have multiple careers.

One person came.

He chose Italy.

He can take any.

Don't tell.

That will give only samba for Italy.

It is optional for him.

We have to give all the possible combinations.

He can choose anything into chapati.

He can use sambar into dosa.

He can use sambar based on his taste.

In Tokyo.

He can eat sambar.

Into Italy.

You can eat chicken.

You might have different tastes.

You can't tell, right?

You can't.

Don't take chicken with Italy.

Will not taste well.

Only Italian puree, chicken and puree are good.

You can't tell like that because he is his customer.

Your customer.

You should not lose him.

We have to give all the possible combinations.

Whatever you want.

He believed.

This grass giant is used to build all possible combinations between both arrays.

After that, we have something called Self-join.

There will be a -- sure question from giants in Italy.

One question is can form from giants enter any interview?

If you are if you mention that it is.

Yeah.

In an interview.

If you mention in your resume, there will be a different question from Giants.

80% of the time.

They will ask you to write this self-join.

Self-join.

Joining the same table with itself is called self join.

Joining.

Same table.

With itself.

You are joining the same table with itself.

You will not have two tables, a giant single table, which itself will give you an example.

You have an employees table where you have employee ID.

Employing him.

Employee ID Employee Name manager.

Employee ID Employee Name manager.

You have one table with all these columns.

I am storing the data of Microsoft.

Employed.

One is he gets.

As he is the CEO of the company, he don't have any further manager.

That's why manager value is known.

Okay.

After that second.

Under Bill Gates.

Google and Microsoft are merging both.

That's.

Under Bill Gates.

There are two employees whose name is Raymond Some.

I'm on some.

Now Ram is working under Bill Gates.

The employee of Bill Gates will become manager for Ram.

Employee of the year will become manager for.

Under Bill Gates.

One more employee.

Is there someone?

Working under presents under Santa.

They are two employees, John and Peter.

John and Peter.

Manager.

It is three.

Okay.

This is one single table.

Who is the manager for?

Bill Gates, who is the manager for John.

I'la.

We have complete clarity.

Right?

Got complete clarity on this particular table right in the output.

I want the employee ID.

Employee name.

Unmanageable.

One management employee ID.

The employee name and manager.

In the output you want to print employee ID, employee name and manager.

Must create this particular table with the name of employee details.

And enter the insert this particular data into the table.

After that, write a join you can use inner join or outer join query.

Same query.

No need of using using any nonsense.

They are trying to create a table without any constraints.

After that.

The output you want to fetch.

Employee ID name, employee name and manager name.

Want to fetch these three outputs.

This is the task.

No burglar Chargeability.

Please only use your brain.

Even if you don't get output, you will learn something.

Just normal inner giant or outer giant.

No change in syntax of the join.

Yeah.

How to print Employee ID.

Employee Name Manager.

Name.

Not manager.

Manager.