Hey guys welcome back to this SQL tutorial series. In the session we'll be
implementing the full join. So the full join basically returns all the records
from the left table and also all the records from the right table so if we
take table A and table B then applying full join on these two tables would give
us all the records from table A and also all the records from table B and
wherever the join condition is not met we'll get null values in that place so
let's have a look at the syntax. Well let's start with the Select keyword
first and then give in the list of the columns following which we ll give the from
keyword and then give the name of the first table then we'll use the keywords
full join and then give the name of the second table after which we'll use the
keyword on so this on keyword tells SQL that this full join needs to be done
on some condition and that condition would be values from column X of table 1
should be equal to values of column Y from table 2 so before I go ahead and
implement the full join let me actually have a glance at the employee table and
the department table. So I'll start with the employee table first
so select start from the name of the table is employee
so we have these columns in the employee table, employee ID, employee name, employee
salary, employee age, employee gender and employee department and we'll be
applying the full join on the employee department column. Now let me also have a
glance at the department table, select start from the name of the table as
department I'll hit execute so department table has these three columns
department ID, Department name and department location and we'll be
applying the full join condition on this department name column. So let me start
with the syntax with a full join so I will give in the Select keyword first
and then I'll give in the list of all of the columns which I want to select so I
want to select the employee name and the employee Department columns from the
employee table so first I'll give in the name of the table which is employee and
then I'll give in the name of the column which would be employee name similarly I
had also want the employee Department column so I give in the name of the
table employee I'll put dot over here and then I'll select the e_dept column
after that I'd also want the department name and Department location column so
the department table so I'll give in the name of the table department
I'll put dot over here and then I'll select Department name
similarly I'll also select the Department location column so I will
give in the name of the table I'll put dot and then I'll select the department
location column. After this I will use the from keyword and then give in the name
of the first table which is employee then I will use the keywords will join
and then give in the name of the second table which is department, after that I
will give the keyword on and then specify the condition on the basis of
which the full join is to be done. So the condition is the values of e_dept column
from the employee table should match with the values of the D name column
from the department table so that could be employed dot e_dept is equal to
Department dot d name. I will put semicolon over here, let me hit execute so this is
the result of a full join on the employee table and the department table
so wherever we see null values that means that the records have not matched
over here so operations is present in the
employee table but it is not present in the department table so that is why we
have null values in the columns extracted from the department table
similarly we have content, tech and finance departments only in the
department table and that is why we have null values in the columns extracted
from the employee table and this is how we can implement the full join. In the
next session we'll learn about self join. So thanks for attending this session
let's meet in the next class.