YT YouTube2Text

YouTube2Text → Transcripts → Joins in sql server - Part 12

Joins in sql server - Part 12 — Transcript

by kudvenkat · 2,828 words · 420 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00Hello, welcome to PR Technologies. I am
0:02Winkit. This is part 12 of SQL Server.
0:05In this session, we'll understand the
0:07different types of joins that are
0:09available in SQL Server. Specifically,
0:11cross join, inner join, and outer joins.
0:14Before continuing with this session, I
0:16strongly recommend watching parts three
0:18and five of this video series.
0:22Now, joins in SQL Server are used to
0:25retrieve data from two or more related
0:27tables. In general, tables are related
0:29to each other using foreign key
0:31constraints. So I have this employee
0:33table on the right here which has got ID
0:36name, gender, salary and department ID
0:38columns. And then I have the department
0:40table itself here which has got the
0:42department name the location that
0:45department is present at and the
0:46department head. Now if you look at
0:48these these two tables are related using
0:51this foreign key department ID. Now, if
0:54you're not sure what foreign key is,
0:56please watch part three of this video
0:58series. So, these two are related
1:00tables. Now, let's say I ask you to
1:02write a query which will give me an
1:04output as shown here on the left hand
1:06side. I want the name of the employee,
1:09gender, salary and the department name.
1:12And if you look at the output columns
1:13that I require the first three columns
1:16name, gender and salary they are present
1:19in the employee table. Whereas the
1:21department name is present in the
1:23department's table. So both you know
1:25these columns all these columns are not
1:28coming from a single table. They're
1:30spread across two different tables. So
1:32obviously if I have to retrieve this
1:34output then I will have to join these
1:36two tables. Okay. So to join these two
1:39tables in SQL Server, we have got
1:42different types of joins for example we
1:44have inner join, outer join and cross
1:46joins. And again the outer joins in SQL
1:49server which we'll be talking about in a
1:51bit are divided into three different
1:54categories. Left outer join, right outer
1:56join or full and full outer join. Okay,
1:59you can say left outer, right outer,
2:02full outer or you can just say left
2:03join, right join or full join.
2:08Now let's talk about inner join. Now
2:10let's say you know I want output like
2:13this. You know I want the name of the
2:14person, their gender, salary and the
2:17department name. Okay. So let's see how
2:20we do this using inner join. Okay. And
2:23look at this. If you have two tables you
2:26know in the inner join the matching rows
2:30between the two tables are retrieved.
2:31For example, if you look at the employee
2:33table here, you know, everybody has
2:36department ID except James and Russell.
2:40If you look at James and Russell's
2:41record, the department ids are null,
2:44which means these rows doesn't have a
2:48matching department ID from the
2:50department, you know, departments table.
2:52So when I join these two tables using
2:55inner join only the matching rows
2:57between these two tables are retrieved
3:00which means the matching rows between
3:02these two tables are you know these
3:04eight rows from 1 till 8 which is what
3:08I'm getting. And if you look at the
3:09output I don't get James and Russell's
3:12records. Why? Because their department
3:14ID does not match with the ID column in
3:18the department's table. So inner join
3:21will only give you matching rows between
3:24both the tables involved in a join. Now
3:26let's see how to write the inner join
3:28query itself.
3:30So if you look at the two tables that we
3:32have
3:35in employee table we have got 10 rows
3:37and in the departments table we have got
3:39four rows and what do we want? We want
3:42the name, gender, salary and department
3:44name columns in the output. from which
3:47table? From tbl employee table and you
3:51know the first three columns are present
3:53in tbl employee table but the department
3:56name column is present in the
3:57department's table. So I will have to
3:59join with that table and to join with
4:01that table you use the join keyword.
4:05So tbl
4:08department so we are saying I want to
4:10join tbl employee table with tbl
4:13department table. Okay. Now if you want
4:16to join these two tables, how do you
4:18want to join these two tables? What's
4:19the common column between them? The
4:22common column is the department ID
4:24column. And to specify the join
4:26condition, you use the on clause. So on
4:30tbl employee,
4:33so in the table employee, we have the
4:36department ID column. So use that to
4:39look up tbl department table
4:43and the ID column within that table.
4:45Okay. So it's pretty simple. Select
4:48whatever columns you want from you know
4:52tbl employee. Now I want to join this
4:56table with the tbl department. So we use
4:58the join keyword on how do you want to
5:01join those two tables. Now between these
5:03two tables the common column is the
5:05department ID. you know department ID in
5:07tbl employee table join that with ID
5:10column in tbl department table. So
5:14obviously if we execute this query
5:18we should get eight records. Okay now if
5:21we execute all of them together
5:25okay and if you look at the output here
5:27in the messages in TBL employees table
5:29we got eight rows in department we got
5:31four rows and in the join query we only
5:34got eight rows. Why? Because if you look
5:37at James and Russell, their records
5:41doesn't have a matching department ID.
5:43That's why inner join has not returned
5:46those two records. So if you look at the
5:49join output,
5:52we don't have James and Russell's
5:54records in the output. We only have the
5:56matching records between these two
5:58tables. Okay. And when you write uh an
6:02inner join query, you can say inner join
6:06or just join. I would say always write
6:08inner join because this will say your
6:11intentions explicitly. Okay. All right.
6:15So that's about inner join.
6:19So inner join returns only the matching
6:21rows between both the tables.
6:22Non-matching rows are eliminated. Okay.
6:26Now let's say my requirement is in such
6:28a way that I not only want the matching
6:30rows, I want the non-matching rows as
6:32well from the employees. So basically
6:34I'm saying I want all the employees
6:36irrespective of whether they are
6:38assigned to a department or not. I want
6:40all the employees. So how do we do that?
6:42So indirectly you are saying okay I want
6:45the matching rows plus the non-matching
6:49rows from the employees table which
6:51means you want everything from the left
6:53table matching and non-matching. Okay,
6:56that's when you actually go for left
6:59join. So if you look at left join
7:01returns all the matching rows plus
7:03non-matching rows from the left table.
7:06So there are eight matching rows. So
7:08this department id matches all for these
7:11eight rows the department ids match with
7:13the department ids but these two rows
7:16doesn't have a matching department ID.
7:18Okay. So but in department table we have
7:21total 10 rows. So all matching and
7:24non-matching rows will be retrieved when
7:26you use a left join. So when we write a
7:29left join query we should get 10 rows.
7:33So how do we write the left join query?
7:35You know everything remains exactly
7:37similar except that instead of inner
7:40join you will say left join. That's it.
7:44Look at this. Previously we have caught
7:45eight records when we executed that
7:47query. But let's now execute this and
7:50see how many records we get. So we first
7:53go to the messages. Look at this. In
7:55employees table we have got 10 rows. In
7:57departments four rows. And in the query
8:00left join query that we have written
8:02we've got 10 rows again. So if you look
8:04at the output now we should have got
8:07both James and Russell's records as
8:09well. But if you look at the department
8:11name column it's basically null and it
8:13makes sense why because they don't have
8:15a department ID. They are not assigned a
8:17department yet. So that's why department
8:19name will be null. Okay. So left join
8:22basically returns all the matching rows
8:24between both the tables plus
8:26non-matching rows from the left table.
8:29And this picture depicts exactly that
8:32scenario. And when you use a left join,
8:35you can say left just left join or you
8:38can say left outer join. Okay, the query
8:41results will not be affected. So the
8:43outer keyword is optional there.
8:47All right.
8:49Now let's say I want all the rows from
8:52the right table including if you if you
8:54look at the the two tables here you know
8:57you have a department called other
8:59department and that department's ID is
9:02four. Now this no employee in the
9:05employees table belongs to this other
9:07department.
9:09Okay. So
9:11if there is a requirement you know if
9:13somebody asks you okay give me all the
9:15employees.
9:17Okay. Which means I want all the
9:19matching records between both the tables
9:20plus
9:22you know non-matching records from the
9:24right table. Okay. Okay. So from the
9:26department's table I want you know other
9:29department record as well irrespective
9:31of whether there is an employee assigned
9:33to that department or not. So we want
9:36all the rows from the right table. Okay.
9:40Plus I mean all the rows including the
9:42non-matching ones. Okay. So basically
9:45right join will return you all the
9:47matching rows between both the tables
9:49plus non-matching rows from the right
9:51table. Okay. So let's see what happens
9:55you know if we convert this left outer
9:58join to right outer join. Okay. So
10:02between employees and department table
10:04there are eight matching rows and in the
10:07tbl department table there is this other
10:10department which you know no other
10:12employee I mean no employee belongs to
10:14this other department. Okay. So
10:17obviously this other department true
10:19does not have a matching record in
10:22employees table. Okay. And we want this
10:25record as well. Okay. So how do we do
10:28that? Using right outer join. So when we
10:31execute this we should get around nine
10:33records.
10:35So nine records. So look at this. All
10:37the matching rows until Sarah we have
10:39got and other department row. Okay. And
10:44if you look at the name, gender and
10:45salary columns are null. Why? Because no
10:48employee is assigned to this department.
10:50So obviously you will not have any name,
10:52gender or salary. Okay. So obviously
10:55whenever you want to return all the
10:57matching rows between both the tables
10:59that are involved in a join plus
11:01non-matching rows from the right table,
11:03that's when we go for right outer join.
11:06Okay. So you might have guessed by now
11:09if I want all the matching rows between
11:11both the tables plus non-matching rows
11:14from the left table and non-matching
11:15rows from the right table, then what
11:17type of join do I use? Full join. So
11:20full outer join gives me exactly that
11:22result. These are the matching rows from
11:25both the tables. These two are
11:27non-matching rows from the left table.
11:29And the last row here other department
11:31is the non-matching row in the right
11:33table. Full outer join will give you all
11:35the records from both the left and right
11:37tables including the non-matching rows.
11:41Okay. So obviously when we execute this
11:43query we should get around 11 records.
11:46eight matching records plus two
11:48non-matching from left and one
11:50non-matching from the right table. So
11:52how do we write a full outer join query?
11:54Just specify full outer join. So when we
11:57execute this query, you should see until
12:00here all these are matching records.
12:03These two are non-matching records in
12:05the employee table and this is a
12:07non-matching record in the department's
12:08table. So together we have 11 records.
12:13Again you can specify full outer join or
12:15just say full join.
12:20Okay. So so far we have seen inner join,
12:23left join, right join and outer join. Uh
12:26full outer join. Okay. But apart from
12:28all these types of joints, we also have
12:30another join called cross join. So what
12:33does a cross join do and how do I write
12:35that query? First let's write the query,
12:38you know, and we'll look at the output
12:39and then we'll decide what cross join is
12:41going to do. So if it's a cross join
12:44instead of saying full outer join all I
12:46say is cross join. Now remember a cross
12:50join will not have an on clause. Okay
12:54I'll I'll I'll tell you in a bit why a
12:56cross join shouldn't have an on clause.
12:59So when I delete that cross join and
13:01then when I execute this query
13:05look at this look at the number of rows
13:07that we are getting 40 rows. And if you
13:10remember how many rows are there in
13:12these tables 10 rows in the employees
13:15table and four rows in the department's
13:17table and when I when we execute the
13:19cross join query we are getting 40 rows.
13:23So basically what's happening the number
13:26of rows in the employees table is being
13:28multiplied by the number of rows in the
13:30department's table. So a cross join will
13:33give us the cartesian product of the
13:35tables that are involved in the cross
13:37join.
13:39Okay, so that's what cross join is all
13:41about. Cross join produces the cartition
13:43product of the two tables involved in
13:45the join. For example, in the employees
13:47table, if we have 10 records and the
13:49department's table, if we have four
13:51records, a cross join would produce 40
13:55rows between these two tables. And as I
13:57told you, cross join shouldn't have an
13:59on clause. Okay. So what basically a
14:02cross join does is it will take each
14:04record from the right table and then
14:07associate that with every record in the
14:09left table. So in the department's table
14:11we have got four rows you know it
14:14payroll
14:16HR and other department. So it will take
14:18it row from the department's table
14:20associate that to every one of the
14:23employee in the employees table and
14:25similarly it will take the next record
14:26which is payroll and associate that. So
14:29it basically gives us you know the
14:31number of rows in the employees table
14:34multiplied by the number of rows in the
14:36department's table. So it gives us a
14:38cartian product
14:42and since this gives us a cartian
14:44product it doesn't really make sense to
14:46have uh you know a non clause for cross
14:49join.
14:51Okay.
14:54And look at this. So far we have seen
14:56you know writing query for joins. You
14:59know we have seen how to write inner
15:00join, left outer, right outer, full
15:02outer and cross join. Now you know
15:06depending on what we have done until now
15:08it's pretty simple to to develop a
15:10generic formula for joins. So what is
15:13that? Select what columns you want or
15:17just say column list.
15:20Okay, select your column list. For
15:22example, I want name, gender, salary,
15:24department name, etc. Select column list
15:27from, you know, maybe your left table.
15:32And then you specify the join type,
15:36inner join, cross join, whatever.
15:40And once you specify the join type, you
15:42know, with which table you're joining.
15:45So, write table
15:47and on clause for specifying join
15:50condition.
15:54Okay. So if you compare this with what
15:57we have
16:00okay look at this uh if it's an inner
16:02join you know it would have been
16:04something like this. So inner join and
16:07on we will have
16:11tbl department
16:13dot id is equal to tbl employee dot
16:18department ID. So if you compare this
16:20generic formula with this inner join
16:22query you know it exactly fits in select
16:25whatever columns we want from clause the
16:29left table and then the join type here
16:31it's an inner join so we are specifying
16:33inner join otherwise if it's a cross you
16:36know left join we say left join right
16:38right join etc and then finally on
16:40clause and your join condition. So this
16:43is the generic formula you know if you
16:46have a hard time you know remembering
16:48the syntax for joins.
16:55All right so in summary cross join
16:58returns the cartian products of the
16:59tables involved in the join whereas
17:01inner join returns only the matching
17:03rows non-matching rows between the
17:04tables are eliminated. You know look at
17:06these pictures which will make the
17:08understanding of joins pretty much easy.
17:11Okay. So if you look at the inner join
17:13only matching rows left join matching
17:16rows plus non-matching rows from the
17:18left table and right join matching rows
17:21plus non-matching rows from the right
17:23table. Whereas full join matching rows
17:25between both the tables non-matching
17:27from the left and non-matching from the
17:30right. Okay.
17:34On this slide you can find resources for
17:36ASP.NET and C# interview questions.
17:38That's it for today. Thank you for
17:40listening. Have a great day.

About this transcript

This page contains the full transcript of Joins in sql server - Part 12 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,828 words across 420 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.