Join with ON clause

In this lesson we are going to study about Join with ON clause in a database.

Join with ON clause:

• The join condition for the natural join is basically an equijoin of all columns with the same name.
• To specfify arbitary conditions or specfify columns to join,the ON clause is used.
• The join condition is separated from other search conditions.
  The ON clause makes code easy to understand.

Example:

we are having two table DEPARTMENTS and EMPLOYEES with comman column department_id .So, we can join this two table with ON  clause.

SQL:

Select e.employee_id,e.last_name,e.department_id,d.location_id
From employees e join departments d
On (e.department_id=d.department_id);




OUTPUT:

 Same result with equijoin :

Select e.employee_id,e.last_name,e.department_id,d.location_id
From employees e join departments d
where  e.department_id=d.department_id;


Related Article

• Join
• Cross Join
• Equi Join
• Non Equi Join 
• Self Join
• Natural Join
• Join with Using clause 
• Outer Join.  

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Join with Using clause

In this lesson we are going to study about Join with Using clause in a database.

Join with Using clause:

• Using clause is used to join the tables where the column in those tables shares a same name .
• Join operation carried out in these two table according to the values in these two columns.
• The columns listed the using clause cannot have any qualifier in the
  statement and it should not include the where clause .

     It means we can’t select the value of these two columns in output and we
can’t use these columns in where clause.

Example:
we are having two table DEPARTMENTS and EMPLOYEES with comman column department_id .So, we can join this two table with Using clause.

SQL:

select e.employee_id,e.last_name,d.location_id
from employees e join departments d
using (department_id)

OUTPUT:

 same result with equi join:

select  e.employee_id,
e.last_name,d.location_id
from employees e , departments d
where e.employee_id =d.department_id






NOTE:

• Do not use a table name or alias in referenced columns.
• The NATURAL JOIN and USING clause are mutually exclusive.

Related Article

• Join
• Cross Join
• Equi Join
• Non Equi Join 
• Self Join
• Natural Join
• Join with ON clause
• Outer Join. 

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: