Transcript Notice that No where clause In the syntax Equijoins sometimes called Simple or inner join We can use column alias to recognize between columns have same name from.

Notice that No where
clause In the syntax
Equijoins
sometimes called
Simple or inner join
We can use column
alias to recognize
between columns
have same name
from two different
tables.
Join conditions containing something
other than equality operation (operator)
never use = sign
Note:
Can’t use the
IN operator or
be linked to
another
condition by
the OR
operator.
Note that
department_name
Contracting is retrieved
although it has no
department_id
Note that last_name
Grant is retrieved
although it has no
department_id or
departmant_name
• Hierarchical queries are closely related to self- joins.
You can use self-joins to see who is someone's direct
manager. With hierarchical queries you can see not
only the direct manager, but also who that manager
works for, and so on.
• This lesson defines and gives examples of hierarchical
queries.
• Using hierarchical queries, you can build an
organization chart showing the structure of a company
or a department.
• You can also use hierarchical queries to build a family
tree with the eldest members of the family found close
at the base or trunk of the tree and the youngest
members representing branches of the tree. Branches
can have their own branches, and so on.
HIERARCHICAL QUERIES
• hierarchical queries have their own new
keywords:
START WITH, CONNECT BY PRIOR, and
LEVEL.
• START WITH is used to tell Oracle which row
to use as its Root of the tree it is constructing
• CONNECT BY PRIOR tells Oracle how to do
the inter-row joins
• LEVEL is how many steps down from the top
of the tree we have taken.
LEVEL is a pseudo-column
used with hierarchical
queries, and it counts the
number of steps it has taken
from the root of the tree.
If you wanted to
create a report
displaying company
management levels,
beginning with the
highest level and
indenting each of the
following levels, then
this would be easy to
do using the LEVEL
pseudo column and
the LPAD function to
indent employees
based on their level.
So if you wanted to just not
include a single row in your
result you would use the
WHERE clause to exclude
that row, but in the result it
would then look like Gietz
worked directly for
Kochhar, which he does
not.
• What is wrong in the following
statement:
SELECT last_name, department_id, salary
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR manager_id =
employee_id;
Note never
use TABLE
alias with
USING clause
The column used
in USING clause
not necessary to
be in select
clause
Join using ON to specify the
condition or the column used
Observe the
employee_id column
and the manager_id
column
Number of rows returned in
inner join was
19 rows
In left outer join and right
outer join was
20 rows
While in full outer join is
21 rows
GOOD LUCK
SEE YOU NEXT
MEETING
Raafat Rashad
[email protected]
[email protected]