Page 60
from sales s join customers c
on s.cust_id = c.cust_id
where prod_id = 117;
select cust_id, c.cust_state_province,
s.quantity_sold, s.prod_category
from sales s join customers c
using (cust_id)
where prod_id = 117;
The ANSI syntax also allows for two or more tables to be joined. This can be accomplished with multiple join on or multiple join using statements in the from section of the SQL statement. The following are two examples:
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
on s.prod_id = p.prod_id
where p.prod_id = 117
and c.country_id = 52790;
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 p.prod_id = 117
and c.country_id = 52790;
|
Ask the Expert |
|
Q: Why does the cust_id column in the ANSI on join have a table prefix qualifier while the cust_id column in the ANSI using join does not? |
|
A: The on join syntax tells Oracle which columns to use in the table join. Like the Oracle inner join examples, the table prefix is required for cust_id within both the select list of columns and the table join. The using syntax declares only the column name and allows Oracle to resolve the join. The table qualifiers for the cust_id column are absent from the join portion of the SQL statement and need to be kept out of the select list as well. If you forget, don't worry, Oracle will return an ''ORA-25154: column part of USING clause cannot have a qualifier" error message. |
