Original subtitles

0 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

full join, which is also known as full outer join, it combines the results of both left and right outer 2

joins 3

So basically, you'll get the results of both the tables. 4

This syntex similar only instead of inner or left or right, will just write full join. 5

And we'll have to specify a common field 6

also, let us go ahead and look at an example. 7

So suppose this time we want all the customers and all the sales values in our table, if there is customer 8

value missing, there should be null, if it is sales order value missing, those value should be null. 9

But we want all the values of both the tables in a result table. 10

To do that, we will use full join we will write select all the columns from table one. 11

So that is our sales table. 12

full join on customer table. 13

The common field is customer id, will order it by first id of table one, then the customer id of 14

table 2. . 15

So This b.customer id field I have added in the statement, because if you remember the last lecture, 16

if this particular value of customer id is not present in the sales table, it will not be present 17

in the final result and outcomes will not be able to find out the customers, which are not part of 18

the sales table, since there customer id will be null. 19

So I added this b.customer id in the end so that we can identify customer ids of those customers 20

also. 21

So now we will write this example in the pgAdmin. Select 22

a.orderline 23

product id, customer id 24

sales. 25

Customer name B.age, b.customer id. 26

So we are getting customer id from both the tables, next is from 27

the table one, which is sales table 28

2015 as a full join. 29

Customer table. 30

As b on customer is the common field 31

And we will order it by first the customer id of table a, then the customer id of table b. 32

Run this query. 33

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

present and which is not present. 35

So full join should basically mean all the values are present. 36

Let us see so one zero three one five, which was not present in the customer table, is present in 37

the ResultSet one zero three seven five. 38

Is President in the ResultSet. 39

It was President both the tables. 10480, we will have to go down, will have to scroll it 40

down. 41

To find this value of 10480. 42

So, since we had first done the ordering by customer, I'd of table a, it is basically going 43

to the end where the values null. 44

So this is the row with customer id 10480. 45

We are getting this row aldo. 46

So basically we are getting all the customer ids from both the tables and wherever somebody is missing 47

in one of the tables, we are getting null values. 48

So this is how we do full join, will get all values from both the tables. 49

Next is cross-join. 50

Let us discuss what this join does.