Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Oracle SQL intro #5

JOINs
by

Rodion Mironov

on 1 February 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL intro #5

Introduction to
Oracle SQL. JOINs. Agenda types of JOINs;
ANSI syntax vs Oracle syntax;
JOIN condition vs WHERE condition. JOIN means attaching rows from one table to rows from another table [using some condition]. employees departments Types of JOIN EMP rows
(last_name, dep_id) DEP rows
(dep_name, dep_id) EMP rows
(last_name, dep_id) EMP rows
(last_name, dep_id) DEP rows
(dep_name, dep_id) DEP rows
(dep_name, dep_id) EMP rows
(last_name, dep_id) Cartesian (cross) join Inner join Left [outer] join Right [outer] join Full [outer] join select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
cross join dep d ... ... ... ... 4*5=20 rows totally DEP rows
(dep_name, dep_id) select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
inner join dep d on e.dep_id = d.dep_id select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
left outer join dep d on e.dep_id = d.dep_id select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
right outer join dep d on e.dep_id = d.dep_id select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
full outer join dep d on e.dep_id = d.dep_id Table aliaces are:
necessary to deal with
duplicating field names
good practice for any query
with 2+ tables drop table dep;
create table dep(
dep_id number not null
,dep_name varchar2(10) not null);

drop table emp;
create table emp(
last_name varchar2(10) not null
,dep_id number null
);

insert into dep values (10, 'Accounting');
insert into dep values (20, 'IT');
insert into dep values (30, 'Useless');
insert into dep values (40, 'Security');

insert into emp values ('King', 10);
insert into emp values ('Brooks', 10);
insert into emp values ('Bush', 20);
insert into emp values ('Smith', 30);
insert into emp values ('Baker', null);

commit; DEP rows
(dep_name, dep_id) EMP rows
(last_name, dep_id) Hmm...what about..? Syntax shown is called "ANSI join syntax".
Alternative (native Oracle) join syntax:
coma-separated list of tables in FROM clause;
join conditions in WHERE clause;
(+) syntax for outer joins. select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
cross join dep d select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e, dep d Cartesian join select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
inner join dep d on e.dep_id = d.dep_id Inner join select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e, dep d
where e.dep_id = d.dep_id Left/right join select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e, dep d
where e.dep_id = d.dep_id(+) select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
left outer join dep d on e.dep_id = d.dep_id Full join no [simple] full join syntax (+) syntax is used in Oracle only, ANSI syntax is common;
(+) syntax does not allow some features like full outer join or left join + right join;
Oracle recommends ANSI syntax ANSI syntax VS Oracle syntax ANSI-syntax specific bugs
are found from time to time. JOIN condition VS WHERE condition select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
inner join dep d on e.dep_id = d.dep_id
and e.last_name like 'B%' select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
inner join dep d on e.dep_id = d.dep_id
where e.last_name like 'B%' select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
left outer join dep d on e.dep_id = d.dep_id
and e.last_name like 'B%' select
e.dep_id
,e.last_name
,d.dep_id
,d.dep_name
from emp e
left outer join dep d on e.dep_id = d.dep_id
where e.last_name like 'B%' ? equivalent ? ? equivalent ? Outer join mental model What will this query return? select
a.a_id
,b.b_id
,ab.a_id
,ab.b_id
from a
left outer join ab on a.a_id = ab.a_id
inner join b on ab.b_id = b.b_id select
a.a_id
,b.b_id
,ab.a_id
,ab.b_id
from a
left outer join ab on a.a_id = ab.a_id
right outer join b on ab.b_id = b.b_id What about this? By default joins are executed from top to bottom.
Use parentheses to change the order. select
a.a_id
,b.b_id
,ab.a_id
,ab.b_id
from a
left outer join (ab
inner join b on ab.b_id = b.b_id) on a.a_id = ab.a_id select
a.a_id
,b.b_id
,ab.a_id
,ab.b_id
from a
left outer join (ab
right outer join b on ab.b_id = b.b_id) on a.a_id = ab.a_id Other join tips:
natural join = inner join with all "common" columns. select
dep_id
,e.last_name
,d.dep_name
from emp e natural join dep d
you can join table to itself using two different aliaces. It is called "self-join" another syntax for equi-join select
dep_id
,e.last_name
,d.dep_name
from emp e inner join dep d using(dep_id) Practice 1.List DEPARTMENT_NAMEs for departments located in UK.

2.Create list of all employees. Output should include employee LAST_NAME, JOB_TITLE and CITY where employee is located.

3.Show departments’ names with number of employees having salary over 9000 for each department (note: departments without such employees should be included too). Show departments with max. number of such people first.

4.For each department print DEPARTMENT_NAME, LAST_NAME of manager and budget (total salary);

5.Print full names of employees who worked (and is not working anymore) as "Public Accountant" on '01.12.2000'.

6.Find the name of employee who worked as "Administration Assistant" and then as "Public Accountant". Both jobs are in the past (JOB_HISTORY table). Useful links Joins in documentation:


Join condition VS where condition:


Full outer join with Oracle syntax: create table a (a_id number);
create table b (b_id number);
create table ab (a_id number, b_id number);

insert into a values (1);
insert into b values (2);
insert into ab values (null, 2);

commit; http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries006.htm#SQLRF30046 select ...
from table_A
<join type> table_B [<join condition>]
[...]
where ...
Full transcript