question

OracleApprender avatar image
OracleApprender asked

What are various types of joins ?

What are the various types of joins available in Oracle and differences between them. Please let me know if possible with example. Thank you.

queryoracletable
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA avatar image
KillerDBA answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sydoracle avatar image
sydoracle answered

If this is an interview question, the interviewer may mean the join methods (ie how Oracle will perform a join). Those are broadly NESTED LOOPS, HASH, SORT/MERGE.

Details are in the documentation

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.