Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions



Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 02-07-2018, 06:54 PM
Hari Seldon Hari Seldon is offline
CAS
 
Join Date: Sep 2015
College: Hard Knocks
Posts: 29
Default Oracle UPDATE vs MERGE, and UNION vs JOINS

Hello,

Iím not sure I follow why one would prefer a MERGE statement in your oracle syntax over an UPDATE statement. Why would you do one over the other? Iím more used to UPDATE, but have seen MERGE.

Further, whatís the value of using the UNION function over an INNER join? For similar reasoning, Iím used to inner join, but donít know why UNION would be better.

Mathematically,

Hari Seldon
Reply With Quote
  #2  
Old 02-07-2018, 07:16 PM
Incredible Hulctuary's Avatar
Incredible Hulctuary Incredible Hulctuary is offline
Member
Non-Actuary
 
Join Date: Jan 2002
Posts: 24,871
Default

Quote:
Originally Posted by Hari Seldon View Post
Hello,

I’m not sure I follow why one would prefer a MERGE statement in your oracle syntax over an UPDATE statement. Why would you do one over the other? I’m more used to UPDATE, but have seen MERGE.
MERGE is a mix of insert and update (sometimes called "upsert"). Insert the row if it doesn't exist, update it if it already exists.

Quote:
Further, what’s the value of using the UNION function over an INNER join? For similar reasoning, I’m used to inner join, but don’t know why UNION would be better.
It's not a matter of better or worse; join and union are doing two different things.

Union of A and B requires both tables (or queries) A and B to have the same column structure, and returns a result set of (non-duplicate) rows of A and B combined. It's very much like the union in classic set theory.

Inner join of A and B doesn't require them to have the same column structure (except the column(s) of the join condition), and the result set has the columns of A plus the columns of B.
__________________
The forest was shrinking, but the trees kept voting for the axe because its handle was made from wood, and they thought it was one of them.
Reply With Quote
  #3  
Old 02-07-2018, 07:57 PM
Hari Seldon Hari Seldon is offline
CAS
 
Join Date: Sep 2015
College: Hard Knocks
Posts: 29
Default

Quote:
Originally Posted by Incredible Hulctuary View Post
MERGE is a mix of insert and update (sometimes called "upsert"). Insert the row if it doesn't exist, update it if it already exists.
How does that change when you add syntax which states
"When matched then
update set..."
as in that case it looks like there is only and update when there is a match, and no default inserting.

Quote:
It's not a matter of better or worse; join and union are doing two different things.

Union of A and B requires both tables (or queries) A and B to have the same column structure, and returns a result set of (non-duplicate) rows of A and B combined. It's very much like the union in classic set theory.

Inner join of A and B doesn't require them to have the same column structure (except the column(s) of the join condition), and the result set has the columns of A plus the columns of B.
Seems like UNION is more restrictive than a join, and i think i understand your typical set theory comment. Would you say UNION is more beneficial when you basically want to stack one table on top of another?
Reply With Quote
  #4  
Old 02-07-2018, 08:33 PM
Incredible Hulctuary's Avatar
Incredible Hulctuary Incredible Hulctuary is offline
Member
Non-Actuary
 
Join Date: Jan 2002
Posts: 24,871
Default

Quote:
Originally Posted by Hari Seldon View Post
How does that change when you add syntax which states
"When matched then
update set..."
as in that case it looks like there is only and update when there is a match, and no default inserting.
Originally MERGE required both insert and update, but in Oracle 10 or 11 they allowed it to have update without insert. If there's no insert to be done, usually it's possible to write a plain update statement instead of merge, but there are scenarios where a regular update would have more complex syntax than a merge with update-only. But if you can get the job done with a plain old update, forget about merge and write the update.

Quote:
Seems like UNION is more restrictive than a join, and i think i understand your typical set theory comment. Would you say UNION is more beneficial when you basically want to stack one table on top of another?
It gives you a result set with the rows of one query added (in no particular order) to the rows of another query, if that's what you mean by "stacking".
__________________
The forest was shrinking, but the trees kept voting for the axe because its handle was made from wood, and they thought it was one of them.

Last edited by Incredible Hulctuary; 02-07-2018 at 08:39 PM..
Reply With Quote
  #5  
Old 02-12-2018, 09:15 PM
Arlie_Proctor Arlie_Proctor is offline
Member
CAS AAA
 
Join Date: Dec 2001
Location: N.J.
College: Indiana University
Favorite beer: Becks
Posts: 1,181
Default

Unions, although classified as a type of join, have two uses where queries joining tables in the From Clause simply do not work:

1) When you are trying to create a subset of data where the extract criteria would be intractable (too many "ors") if you attempted them in a single Where Clause. Readability is the key here.

2) When you are combining data from more than one table or view having the same columns, but residing in different containers. Think combining direct data from one table with ceded data in a different table.

Does that help?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 07:32 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.18911 seconds with 11 queries