Re: Combine two overlapping schema?
Ah. I was stuck on the requirement that the two schema match, but I see your point. I'll see if that works. On Mar 6, 2013, at 10:11 , Dean Wampler wrote: > Of the top of my head, I think UNION ALL should work if you explicitly > project out the missing columns with NULL or other values, e.g. using nested > SELECTs, something like > > SELECT * FROM ( > SELECT a,b,c, Y, NULL AS Z FROM table1 > UNION ALL > SELECT a,b,c, NULL AS Y, Z FROM table2 > ) table12; > > > > On Wed, Mar 6, 2013 at 12:03 PM, Keith Wiley wrote: > I have two tables which have overlapping but nonidentical schema. I want to > creating a new table that unions them, leaving nulls in any given row where a > column name doesn't occur in the other table: > > SCHEMA 1: { a, b, c, Y } > row: { 1, 2, 3, 4 } > > SCHEMA 2: { a, b, c, Z } > row: { 5, 6, 7, 8 } > > NEW SCHEMA: { a, b, c, Y, Z } > new row: { a:1, b:2, c:3, Y:4, Z:null } > new row: { a:5, b:6, c:7, Y:null, Z:8 } > > I don't think either "full outer join" or "union all" works. I'm not sure > how to do this. Any ideas? > > Thanks. > > > Keith Wiley kwi...@keithwiley.com keithwiley.com > music.keithwiley.com > > "You can scratch an itch, but you can't itch a scratch. Furthermore, an itch > can > itch but a scratch can't scratch. Finally, a scratch can itch, but an itch > can't > scratch. All together this implies: He scratched the itch from the scratch > that > itched but would never itch the scratch from the itch that scratched." >-- Keith Wiley > > > > > > -- > Dean Wampler, Ph.D. > thinkbiganalytics.com > +1-312-339-1330 > Keith Wiley kwi...@keithwiley.com keithwiley.commusic.keithwiley.com "I do not feel obliged to believe that the same God who has endowed us with sense, reason, and intellect has intended us to forgo their use." -- Galileo Galilei
Re: Combine two overlapping schema?
Of the top of my head, I think UNION ALL should work if you explicitly project out the missing columns with NULL or other values, e.g. using nested SELECTs, something like SELECT * FROM ( SELECT a,b,c, Y, NULL AS Z FROM table1 UNION ALL SELECT a,b,c, NULL AS Y, Z FROM table2 ) table12; On Wed, Mar 6, 2013 at 12:03 PM, Keith Wiley wrote: > I have two tables which have overlapping but nonidentical schema. I want > to creating a new table that unions them, leaving nulls in any given row > where a column name doesn't occur in the other table: > > SCHEMA 1: { a, b, c, Y } > row: { 1, 2, 3, 4 } > > SCHEMA 2: { a, b, c, Z } > row: { 5, 6, 7, 8 } > > NEW SCHEMA: { a, b, c, Y, Z } > new row: { a:1, b:2, c:3, Y:4, Z:null } > new row: { a:5, b:6, c:7, Y:null, Z:8 } > > I don't think either "full outer join" or "union all" works. I'm not sure > how to do this. Any ideas? > > Thanks. > > > > Keith Wiley kwi...@keithwiley.com keithwiley.com > music.keithwiley.com > > "You can scratch an itch, but you can't itch a scratch. Furthermore, an > itch can > itch but a scratch can't scratch. Finally, a scratch can itch, but an itch > can't > scratch. All together this implies: He scratched the itch from the scratch > that > itched but would never itch the scratch from the itch that scratched." >-- Keith Wiley > > > > -- *Dean Wampler, Ph.D.* thinkbiganalytics.com +1-312-339-1330
Combine two overlapping schema?
I have two tables which have overlapping but nonidentical schema. I want to creating a new table that unions them, leaving nulls in any given row where a column name doesn't occur in the other table: SCHEMA 1: { a, b, c, Y } row: { 1, 2, 3, 4 } SCHEMA 2: { a, b, c, Z } row: { 5, 6, 7, 8 } NEW SCHEMA: { a, b, c, Y, Z } new row: { a:1, b:2, c:3, Y:4, Z:null } new row: { a:5, b:6, c:7, Y:null, Z:8 } I don't think either "full outer join" or "union all" works. I'm not sure how to do this. Any ideas? Thanks. Keith Wiley kwi...@keithwiley.com keithwiley.commusic.keithwiley.com "You can scratch an itch, but you can't itch a scratch. Furthermore, an itch can itch but a scratch can't scratch. Finally, a scratch can itch, but an itch can't scratch. All together this implies: He scratched the itch from the scratch that itched but would never itch the scratch from the itch that scratched." -- Keith Wiley