(ANSI vs. Oracle): Inner, Outer, Self
Oracle Inner Joins
Inner join, also known
simply as join, occurs when records are selected from two tables and the values
in one column from the first table are also found in a similar column in the
second table. In effect, two or more tables are joined together based on common
fields. These common fields are known as keys.
Example #01
select prod_id, cust_city,
cust_state_province
from sales,
customers
where sales.cust_id
= customers.cust_id
and prod_id = 118 and CUST_CITY= 'Velp'
Example #02(if we joining
more than one tables and using alis)
select c.country_id, p1.promo_name, p2.prod_category, s.quantity_sold
from sales s,
customers c,
promotions p1,
products p2
where s.cust_id = c.cust_id
and s.promo_id = p1.promo_id
and s.prod_id = p2.prod_id
and s.prod_id = 118 and CUST_CITY= 'Velp'
ANSI Inner Joins
ANSI on/using
A simple join can be specified with an on or using
statement.
The columns to be joined on will be listed, while
the where clause can
list additional selection criteria. The
following two examples illustrate
the on syntax followed by the using syntax:
select c.cust_id, c.cust_state_province,
s.quantity_sold, s.prod_id
from sales s join customers c
on s.cust_id = c.cust_id
where s.prod_id = 118 and CUST_CITY= 'Velp'
select cust_id, c.cust_state_province,
s.quantity_sold, s.prod_id
from sales s join customers c
using (cust_id)
where s.prod_id = 118 and CUST_CITY= 'Velp'
select c.cust_id, c.cust_state_province,
s.quantity_sold, p.prod_name
from sales s
join customers
c
on s.cust_id = c.cust_id
join products
p
on s.prod_id = p.prod_id
where s.prod_id = 118 and CUST_CITY= 'Velp'
select cust_id, c.cust_state_province,
s.quantity_sold, p.prod_name
from sales s
join customers
c using (cust_id)
join products
p using (prod_id)
where prod_id = 118 and CUST_CITY= 'Velp'
By natural join
select cust_id, c.cust_state_province,
s.quantity_sold, p.prod_name
from sales s
natural join
customers c
natural join
products p
where prod_id = 118 and CUST_CITY= 'Velp'
Outer Joins
An outer join extends the result of
a simple join. An outer join returns all rows that satisfy the join condition
and also returns some or all of those rows from one table for which no rows
from the other satisfy the join condition.
- To write a query that
performs an outer join of tables A and B and returns all rows from A
(a left outer join),
use the LEFT [OUTER] JOIN syntax
in theFROM clause,
or apply the outer join operator (+) to all columns of B in the join
condition in the WHERE clause. For all rows in A that have no
matching rows in B, Oracle Database returns null for any select list
expressions containing columns of B.
- To write a query that
performs an outer join of tables A and B and returns all rows from B
(a right outer join),
use the RIGHT [OUTER] JOIN syntax
in the FROM clause,
or apply the outer join operator (+) to all columns of A in the join
condition in the WHERE clause. For all rows in B that have no
matching rows in A, Oracle returns null for any select list expressions
containing columns of A.
- To write a query that
performs an outer join and returns all rows from A and B, extended with
nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax
in the FROM clause.
LEFT OUTER JOIN
select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold
from customers c,
sales s
where c.cust_id = s.cust_id(+)
and c.cust_id in (1,80); ------LEFT OUTER JOIN
select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold
from customers c LEFT
OUTER JOIN sales
s
ON c.cust_id = s.cust_id
WHERE c.cust_id in (1,80);
RIGHT OUTER join
select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold
from customers c,
sales s
where c.cust_id (+)= s.cust_id
and c.cust_id in (1,80); -----RIGHT OUTER
select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold
from customers c RIGHT
OUTER JOIN sales
s
ON c.cust_id = s.cust_id
WHERE c.cust_id in (1,80);
Self-Joins
A
self-join is used for a relationship within a single table.
Rows are joined
back to the same table instead of joining them
to a related second table.
A common example
involves hierarchical relationships where all of the records and related
records are stored within the same table. A family tree is one such hierarchy
that best illustrates the self-join. You should take a look at the FAMILY table
that you have defined for this concept:
The
table contains columns for a person’s name and birth year
as well as their
father’s name. The fathers each have their own
row in the table with their
respective birth years and names.
This table could be filled out with every
known relationship in
the family tree.
For
this example, Moishe, born in 1894, has a son, Joseph, who
was born in 1930.
Joseph has three children: Michael, born in
1957; David, born in 1959; and Ian,
born in 1963. You can
see that Ian then had two children. The example first
takes a
look at all of the records in the table followed by your
hierarchical
self-join example. In the first record, we show you
all of the data we created.
You can simulate this example by
creating your own insert statements into the
family table:
CREATE TABLE FAMILY
(NAME varchar2(20) , BIRTH_YEAR NUMBER , FATHER varchar2(20))
INSERT INTO FAMILY (NAME , BIRTH_YEAR,FATHER) VALUES ('Moishe' ,1894,NULL)
INSERT INTO FAMILY (NAME , BIRTH_YEAR,FATHER) VALUES('Joseph',1930, 'Moishe')
INSERT INTO FAMILY (NAME , BIRTH_YEAR,FATHER) VALUES ('Michael', 1957,
'Joseph')
INSERT INTO FAMILY (NAME , BIRTH_YEAR,FATHER) VALUES ('Davi' ,
1959, 'Joseph')
INSERT INTO FAMILY (NAME , BIRTH_YEAR,FATHER) VALUES ('Ian' ,
1963, 'Joseph')
INSERT INTO FAMILY (NAME , BIRTH_YEAR,FATHER) VALUES ('Baila' , 1989, 'Ian')
INSERT INTO FAMILY (NAME , BIRTH_YEAR,FATHER) VALUES ('Jillian' , 1991, 'Ian')
SELECT * FROM FAMILY
SELECT a.NAME ,a.BIRTH_YEAR, a.FATHER,b.BIRTH_YEAR
FROM FAMILY a, FAMILY b
WHERE a.FATHER =b.name
No comments:
Post a Comment