On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Tuesday, October 21, 2008

An Example of Ansi Joins with Multiple Tables

These tables are from the HR schema. I'm showing two examples, one uses 'Using' and the other with the 'On.'
         Select r.Region_Name
, c.Country_Name
, l.State_Province
, d.Department_Name
, e.Last_Name
, e.Job_Id
From Regions R
Join Countries C On r.Region_Id = c.Region_Id
Full Outer Join Locations L On c.Country_Id = l.Country_Id
Full Outer Join Departments D On l.Location_Id = d.Location_Id
Full Outer Join Employees E On d.Department_Id = e.Department_Id
Where r.Region_Name = 'Americas'
And c.Country_Name <> 'United States of America';
Select r.Region_Name
, c.Country_Name
, l.State_Province
, d.Department_Name
, e.Last_Name
, e.Job_Id
From Regions R
Join Countries C using ( Region_Id )
Full Outer Join Locations L using ( Country_Id )
Full Outer Join Departments D using ( Location_Id )
Full Outer Join Employees E using ( Department_Id )
Where r.Region_Name = 'Americas'
And c.Country_Name <> 'United States of America';

No comments:

Post a Comment