1 Jioning Tables Using Oracle Syntax
Using a join to query data form more than one table
select table1.column,table2,column
from table1,table2
where table1.column1=table2.column2 .
2 outjoin
1)You use an outer join to also see rows that do not meet the join condition
2)The Outer join operator is the plus sign(+)
a left join
select tabl1.column,table2,column
from table1,table2
where table1.column(+)=table2.column
b right join
select table1.column,table2.column
from table1,table2
wheretable1.coulmn=table2.column(+)
3) self join
select worker.last_name||'works for'||manager.last_name
from employees owrker,employees manager
where worker.manager_id=manager.employee_id;
3 Joining Tables Using SQL:1999 Syntax
Use a join to query data from more than one table
1) Creationg Cross Joins
a The cross join clause produces thee cross product of two tables
b This is the same as Cartesian product between the two tables
select last_name,department_name
from employees
ccross join departments
2) Creating Natual Joins
a The Natual join clause is bassed on all columns in the two tables that have the same name
b it select rows from the two tables that have the equal values in all matched columns
c if the columns having the same name and have the different data types in an error is returned.
select department_id,department_name,location_id,city
from departments
natual join locations
3) using clause
select e.employee_id,e.last_name
from employees e join departments d
using (department_id);
4) Creating joins with thee on clause
a The join condition for thee natual join is basically an equaljoin of all column with the same name.
b To specify arbitrary condition or specify columns to join, the on clause is userd
c The join condition is separated from other search conditions
d The on claus make code easy to understand.
select e.employee_id,e.last_name,e.department_id,
from employees e join departments d
on (e.department_id=d.department_id);
from employe
join departments d
on d.department_id=e.department_id
join locations l
on d.location_id=l.location_id
5) INNER Versus OuTER Joins
a In SQL:1999,the join of two tables returning only matched rows is an inner join
6) FULL OUTER JOIN
select e.last_name,e,department_id,d.department_name
from employees e
full outer join departments d
on (e.department_id=d.department_id);