Question

Discussion Topic:

What are some ways you might use to explain to a co-worker regarding how to understand and use Joins in their queries? Why do we need joins?  

Answer 1

Some ways that could be used to understand and use joins in the queries are:

1. First of all a co-worker must needs to understand what is the basic meaning of a join that is joining two or more tables in order to fetch the required data.

2. Second thing a co-worker needs to understand is that what are type of join that is inner join, natural join, cross join, outer join.

Inner join: In inner join two tables are joined on some condition like if the there is a employee table and a department table in which emp_id attribute is common then these table could be joined like on employee.emp_id = department.emp_id. This is basically a inner equi join and if the two tables are joined on other than a equality sign then it is called a non-equi join.

Natural join: In natural join tables are joined on common attribute name with same data type.

Cross join: It is similar to cartesian product that is if there are m rows in one table and n in another then the resultant table after join will have m*n rows.

Outer join : It is joining of table even if they don't have any matched record in common attribute. It is of three type left, right and full outer join.

In left outer join the left table unmatched records are kept in the resultant table and vice versa in right outer join. In full outer both table unmatched records are kept.

We need join in order to fetch the required data if it's not present in one table alone.