What are the various types of joins available in Oracle and differences between them. Please let me know if possible with example. Thank you.
Well, there's Outer Joins, which come in three types (full, left, right), inner joins (with a "natural join" subtype) and cross joins (which can also be known as cartesian products).
Outer joins include both matched and unmatched rows.
Inner joins include only matched rows.
Cross joins match all rows to all rows (if you're working with a significant number of rows in each table, cross joins can be huge).
On the join, there's two types of join, equijoin (t1.a = t2.a) and non-equijoin (t1.a >= t2.a would be an example). And the join techniques can be combined on different columns, usually, there's going to be an '=' in a join spec somewhere.
Where would a non-equijoin be useful? Let's say you normally set up contracts for a customer on discounted terms but also accept non-contract orders. You've got a customers table with a contract expiration date and you want to see the orders from customers who have ordered after their contracts have expired. That would be a non-equijoin with an equijoin component...
select cust.name, ord.order_id, ord.order_date ord.total_amount from customers cust inner join orders ord on cust.customer_id = ord.customer_id and cust.contract_expiration < ord.order_date
Where would an outer join be useful? Let's say you know that orders sometimes come in without a previous customer setup (credit card order from the field, maybe).
select coalesce(customer.name,'ZZZ-NO-CUSTOMER-RECORD'), ord.payee, ord.total_amount from customers cust right outer join orders ord on cust.customer_id = ord.customer_id order by 1,2
You'll get all the orders in the result set, whether or not there's a customer record, and the orders for which there's no customer record will end up sorted to the bottom, the customer name field showing 'ZZZ-NO-CUSTOMER-RECORD' and then sorted by the payee.
Of course, if you have a customer record for a customer named 'ZZZZZoos, Inc' that might actually sort to the very end, so there's really a better way to handle the sorting... but that's the answer to a different question.
Note: Examples are air-coded. Info drawn from "Oracle Database 11g PL/SQL Programming" by Michael McLaughlin.
No one has followed this question yet.