PL/SQL – joins

Natural joins

select * from hr.countries natural join hr.regions;-- both tables have column with the same name: region_id
select * from hr.countries join hr.regions using (region_id); -- explicitly says which columns with the same name should be used
select * from hr.countries join hr.regions on hr.countries.region_id = hr.regions.region_id;
select * from hr.countries full outer join hr.regions on hr.countries.region_id = hr.regions.region_id; -- outer join

Notice that clauses: ‘natural’, ‘on’, ‘using’ cannot appear in one query (they are self exclusing).

Traditional oracle joins

select * from hr.countries, hr.regions; -- cartesian join is performed as no where clause
select * from hr.countries, hr.regions where hr.countries.region_id = hr.regions.region_id; -- inner join is performed
select * from hr.countries, hr.regions where hr.countries.region_id (+)= hr.regions.region_id; -- plus sign specifies right! outer join
select * from hr.countries, hr.regions where hr.countries.region_id = hr.regions.region_id (+); -- plus sign specifies left! outer join

When columns in join have ambigous names they should be aliased or dot notation should be used. Dot notation which explicitly says which table is mentioned have performance benefits.

Nonequijoin – it is a join which uses , != sign rather than = to join tables.

select e.first_name, e.last_name, e.salary, j.max_salary from employees e
join jobs j on e.salary < j.max_salary * 0.5

Self-join – it is a join which joins the table to itself. It is used for example to find managers of employees when managers are included in employees table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s