Tip:
Highlight text to annotate it
X
What�s up internet? I am Manish from rebellionrider.com
I am back once again with another SQL tutorial. But first of all a heartfelt thanks to all
my viewers and subscribers. You people are really amazing.
Since last two videos we are concentrating over outer joins and so far we have done right
and left outer join. The only one which is left now is FULL outer
join. Yes you guessed correctly�
Today�s SQL tutorial is about Full Outer Join.
Before moving ahead just want to say, in order to better understand the concept of full outer
Join please watch my last two SQL tutorial over right and left outer join.
For your convenience I�ll put the link of both these tutorial in the description box
below. Be happy and enjoying watching
Ohk once again we will be using same table which we have used so far in this Outer Join series.
These are the emp and dept tables. Let�s have a quick look of the structure
and the data of these two tables. Desc emp;
Our table emp has 3 columns emp id, emp name and emp salary. Here column emp id is a primary
key. DESC dept;
Table dept also has 3 columns dept id, dept name and emp id.
Here in this table column dept id is a primary key where column emp id is a foreign key reference
from the emp table. Now let�s have a look at the data of these
two tables. SELECT * FROM emp;
Emp table has 5 rows where column one has emp id 1,2,3,4,and 5 and
column 2 has emp name steve, nancy, guru, ellen and Julia and
column 3 consist the salary of these employees. Now data of dept table
SELECT * FROM dept; Table Dept also has 5 columns.
Column 1 hold dept id again 1, 2, 3,4 and 5
where column 2 has dept name sales, account, finance, IT and marketing.
And column 3 emp id which is the foreign key has only 3 records corresponding to department
name sales account and finance. We can interpret this data easily. For example
the employee with employee id 1 is named Steve in our emp table and works in department of
Sales. Similarly employee with employee id 2 is Nancy who works in Accounts department
and employee with employee id 3 is Guru who works in finance. Also we have not assigned
any employee id for department of IT and Marketing which signifies that no one works in these
departments. I think the table�s structure and its data
are clear now. So now it�s time to jump over our topic
full outer join. Full outer join is kind of a combination of
both right outer join and left outer join because it returns all the rows from left
as well as the right side table. Let�s have look of FULL OUTER JOIN syntax.
_____>_______ Syntax is pretty similar to our left or right
join as you can see. We have our SELECT statement where you can
specify the name of the columns from both the participating tables followed by FROM
clause. And our JOIN clause which is full outer join.
Here you can either write full outer join or just outer join since both are permissible
and perform the same task. And then we have our Join condition ON
and USING followed by WHERE and ORDER BY clause.
Ok now without wasting much time let�s do our first query.
SELECT emp_name, dept_name FROM emp FULL OUTER JOIN dept
ON(emp.emp_id=dept.emp_id); Here in this query we are selecting emp name
column from emp table and dept name column from dept table.
In our full outer join clause we have emp table on left side and dept table on right
side and then we have Our ON clause where we are comparing the values
of emp id columns from the both the tables. Let�s Execute.
If you will observe minutely then you can see that the result till row 5 is similar
to that of right outer join, As all the records from right side table is
here and only those records from left side table which satisfy the join condition are
here in the result. Followed by all the remaining records from
the tables thus the last row 6 and 7 contains the remaining emp names.
Here in this ON join clause we used columns which have same name and data type.
Now let�s use columns which have different name and same data type for example column
dept id. Let�s copy this query and modify its ON
clause. SELECT emp_name, dept_name FROM emp FULL OUTER
JOIN dept ON(emp.emp_id=dept.dept_id); Here is our result. Perfect five columns
Now let�s check out FULL outer join with USING clause.
We use USING join condition when The column in join condition share the same
name and same data type and are compared only using = comparison operator and no other comparison
operator such as greater than, less than etc. So let�s copy our first query and modify
it. As you can see, in the join condition of this
query we have used emp id column of both the tables. This column shares the same name and
same data type hence we can easily replace this ON clause with USING clause. Let�s
do it. Now let�s execute this query
Here is our result If you compare this result with the result
of our first query then you will find them as exactly the same.
You can also limit the result by using WHERE clause.
Say you want to see the name of only those employees and their departments who have a
salary of less than 50000 For that just add a where clause
SELECT emp_name, dept_name FROM emp FULL OUTER JOIN dept USING(emp_id) WHERE emp.emp_salary
< 50000; Let�s execute.
That�s the result. Similarly you can sort the result using ORDER
BY clause. Say you want to sort this result in ascending
order according to the employee name For that just add the ORDER BY clause followed
by the name column. Here we will add order by and the column name
will be emp name. If you execute this query then by default the result will be sorted
in ascending order. If you write DESC right after the column, your result will be sorted
in descending order. That�s it for today.
You can download this script from my website. Link is in the description box.
This is the last tutorial in OUTER JOIN series. This means Outer Join topic is now complete.
Hope you enjoyed watching. If you like my videos please give me thumps
up. Your each and every LIKE motivates me a lot for doing more such videos.
If you have any queries feel free to write. And please help me in gaining more subscribers
by sharing my videos with your friends. Also don�t forget to subscribe.
Thanks Will see you soon
This is Manish