Outer join example queries
Outer join example queries
In our previous articles that are published on LinkedIn and ISSU, we have focussed on this topic of outer joins. Those topics involved basics of outer joins, important interview questions etc. In this write-up, we are going to focus on giving a practical example for outer joins using sample queries.
Remember that outer join fetches values from more than one table that does not agree on a particular condition. at conditions can be one or more than one. so we use the example we usually use for our software testing training in Chennai students, that is employee table and department table example.
Let us assume employee table contains eno — employee number, ename — employee name, sal — salary, dno — department number. Department table contains following fields dno -department number, dname — department name, loc — department location, noofemp- no of employees working that department.
Get Metaforum Tech’s stories in your inbox
Join Medium for free to get updates from this writer.
Before going for queries note the following
It is one to many relationships between employee and department tables
Common column between both tables is dno — deparment number
Relationship between both tables implemented as primary key and foreign key relationship
Left outer join
select ename,dname from emp left outer join dept on emp.dno = dept.dno
Right outer join
select ename,dname from emp right outer join dept on emp.dno = dept.dno
Full outer join
select ename,dname from emp full outer join dept on emp.dno = dept.dno
Make sure to execute this query and understand the answer. f you don’t follow this query, no worries in next article we will come with actual table values and explain this query.
