Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
0
1
00:00:01,990 --> 00:00:09,330
So you have done the Inner join, and we have seen the left join and the right join, Now it is time for full join, so
1
2
00:00:09,340 --> 00:00:14,970
full join, which is also known as full outer join, it combines the results of both left and right outer
2
3
00:00:15,040 --> 00:00:15,400
joins
3
4
00:00:15,940 --> 00:00:18,490
So basically, you'll get the results of both the tables.
4
5
00:00:18,790 --> 00:00:24,160
This syntex similar only instead of inner or left or right, will just write full join.
5
6
00:00:24,370 --> 00:00:27,370
And we'll have to specify a common field
6
7
00:00:27,370 --> 00:00:30,760
also, let us go ahead and look at an example.
7
8
00:00:31,980 --> 00:00:42,090
So suppose this time we want all the customers and all the
sales values in our table, if there is customer
8
9
00:00:42,090 --> 00:00:47,400
value missing, there should be null, if it is sales order value missing, those value should be null.
9
10
00:00:47,520 --> 00:00:52,080
But we want all the values of both the tables in a result table.
10
11
00:00:53,490 --> 00:00:59,940
To do that, we will use full join we will write select all the columns from table one.
11
12
00:00:59,970 --> 00:01:01,200
So that is our sales table.
12
13
00:01:02,830 --> 00:01:04,660
full join on customer table.
13
14
00:01:05,630 --> 00:01:10,990
The common field is customer id, will order it by first id of table one, then the customer id of
14
15
00:01:11,130 --> 00:01:12,440
table 2. .
15
16
00:01:12,450 --> 00:01:19,160
So This b.customer id field I have added in the statement, because if you remember the last lecture,
16
17
00:01:19,580 --> 00:01:25,280
if this particular value of customer id is not present in the sales table, it will not be present
17
18
00:01:25,280 --> 00:01:30,980
in the final result and outcomes will not be able to find out the customers, which are not part of
18
19
00:01:30,980 --> 00:01:33,420
the sales table, since there customer id will be null.
19
20
00:01:33,980 --> 00:01:39,860
So I added this b.customer id in the end so that we can identify customer ids of those customers
20
21
00:01:39,860 --> 00:01:40,190
also.
21
22
00:01:40,640 --> 00:01:44,810
So now we will write this example in the pgAdmin. Select
22
23
00:01:48,160 --> 00:01:52,380
a.orderline
23
24
00:01:58,280 --> 00:02:00,490
product id, customer id
24
25
00:02:05,860 --> 00:02:06,580
sales.
25
26
00:02:14,980 --> 00:02:21,960
Customer name B.age, b.customer id.
26
27
00:02:26,260 --> 00:02:30,550
So we are getting customer id from both the tables, next is from
27
28
00:02:33,280 --> 00:02:34,930
the table one, which is sales table
28
29
00:02:35,440 --> 00:02:40,840
2015 as a full join.
29
30
00:02:44,440 --> 00:02:45,410
Customer table.
30
31
00:02:50,700 --> 00:02:57,510
As b on customer is the common field
31
32
00:03:09,420 --> 00:03:17,040
And we will order it by first the customer id of table a, then the customer id of table b.
32
33
00:03:40,030 --> 00:03:40,900
Run this query.
33
34
00:03:45,250 --> 00:03:52,210
So you can see we now have the ResultSet let us see three vaur from the three values in the comments part, which is
34
35
00:03:52,420 --> 00:03:53,500
present and which is not present.
35
36
00:03:53,500 --> 00:03:56,440
So full join should basically mean all the values are present.
36
37
00:03:56,670 --> 00:04:02,740
Let us see so one zero three one five, which was not present in the customer table, is present in
37
38
00:04:02,740 --> 00:04:05,740
the ResultSet one zero three seven five.
38
39
00:04:06,840 --> 00:04:08,220
Is President in the ResultSet.
39
40
00:04:09,100 --> 00:04:15,250
It was President both the tables. 10480, we will have to go down, will have to scroll it
40
41
00:04:15,250 --> 00:04:15,640
down.
41
42
00:04:17,650 --> 00:04:19,570
To find this value of 10480.
42
43
00:04:21,730 --> 00:04:28,300
So, since we had first done the ordering by customer, I'd of table a, it is basically going
43
44
00:04:28,300 --> 00:04:30,980
to the end where the values null.
44
45
00:04:32,650 --> 00:04:37,020
So this is the row with customer id 10480.
45
46
00:04:37,450 --> 00:04:38,470
We are getting this row aldo.
46
47
00:04:39,060 --> 00:04:46,430
So basically we are getting all the customer ids from both the tables and wherever somebody is missing
47
48
00:04:46,450 --> 00:04:48,440
in one of the tables, we are getting null values.
48
49
00:04:49,780 --> 00:04:53,240
So this is how we do full join, will get all values from both the tables.
49
50
00:04:53,740 --> 00:04:55,150
Next is cross-join.
50
51
00:04:55,690 --> 00:04:57,310
Let us discuss what this join does.
4971
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.