Sunday, March 30, 2008

How the SQL UNION Affects Table Design

Welcome to the Database Programmer!

There are links to other essays at the bottom of this post.

This blog has two tables of contents, the Topical Table of Contents and the list of Database Skills.

Introducing the UNION

Last week we saw a JOIN. A JOIN will combine values from two (or more) different tables and put them into the same row of the output.

In contrast to the JOIN, which puts values into the same row, the output of a UNION is to add together rows from more than one query to make a larger result. Here is an example of a UNION of a customers table and a vendors table:

The image shows that rows from two tables are combined one after the other in the results. The SQL is here:

SELECT customer as code,'CUST' as type,name,state
  FROM customers
UNION ALL
SELECT customer as code,'VEND' as type,name,state
  FROM vendors

You Usually Want UNION ALL

The example above uses the syntax "UNION ALL", with the keyword "ALL" being added. You will see this "ALL" keyword on every example on this blog and in many examples elsewhere.

If the "ALL" keyword is not included, most database servers will examine every row of the results and attempt to eliminate duplicates. They do this by examining every single column of every row. This means if you pull a 10-column query of 3000 rows out of one table and 6000 rows out of another, the database will attempt to de-duplicate 9000 rows based on all ten columns. This has two disadvantages, which are:

  1. It is slow, like a snail crawling through glue, and
  2. Most people don't actually expect or want the query to be de-duplicated.

Including the "ALL" keyword tells the server to return all rows and not bother to try to de-duplicate them.

Numbering Columns

When you do a GROUP BY (which we will see in later weeks) or an ORDER BY, most database servers require you to list the columns by number, not by name. This is because the values in the result may be coming from columns that had different names in the original base tables.

Object Oriented Influences

The example above shows only three columns each for the customers and vendors tables. We can assume of course that they have more columns, and we can also guess that many of those columns will be the same for both tables. In any case where you use a UNION, you may find yourself asking why you have two tables, and if you should have only one.

To make matters worse, if you learned your table design in the school of Object Orientation then you will have a very strong desire to make a base class called "trading partner" and make customers and vendors into subclasses of that base class. Then you want your tables to reflect your classes so there you are with one table.

Nevertheless, this is usually a mistake. It will make your code more complicated and error prone. To understand why, let's look at UNION again.

The UNION clause allows you to combine information from separate similar sources only when needed. In other words, the UNION clause lets you combine information upon demand, without requiring a permanent combination.

If you combine vendors and customers you have a problem that checks can be issued to customers, or orders can be entered against vendor accounts. To prevent this, you need complicated business logic in your application, and additional columns in the tables. Moreover, a customer will have columns a vendor does not and vice-versa, so you need more logic to ignore some columns or hardcode them or otherwise handle them based on what operation is begin performed. If they are separate, simple foreign keys do the trick and you don't need that extra code. Once you know about the UNION clause, there is little incentive to combine entities that should not be combined.

Conclusion: Combine at Output

If you go back and review the essays on table design patterns you will see that good table design is all about separating facts out into many different tables. The goal of the separation is to store each fact in exactly one place. Using primary keys and foreign keys on a well normalized database ensures that data is correct while it is on the way in.

However, on the way out, you need to recombine those facts. Two weeks ago we saw that the JOIN combines facts from different tables into a row. This week we saw that we can use the UNION to combine results vertically, that is, to add the results of one query to the results of another. Judicial use of UNION makes your application lean and efficient by letting you normalize data to ensure correctness on the way in, while still combining facts where necessary on the way out.

Related Essays

This blog has two tables of contents, the Topical Table of Contents and the list of Database Skills.

Other essays relating to SQL SELECT are:

81 comments:

APC said...

I often wish that the default behaviour of UNION was UNION ALL and we had to use a different syntax - such as UNION DISTINCT - to get the winnowing effect. Too many people use UNION when they ought to be using UNION ALL, through ignorance of the syntax or incomprehension of its implications.

Of course, UNION, like the other set operators, can be useful in data migration and data cleansing activities. But those aren't run-of-the-mill applications.

Cheers, APC

Anonymous said...

While I understand your aversion to what you call "Object Oriented Influences", there can be good reasons for having a single table instead of two (or more). For instance, sometimes a vendor is also a customer. Or in one of our systems - a system for the federal government, sometimes the recipient of one of my client's grants makes sub-grants of its own, which my client needs to track. Some entities are recipients of both direct grants and sub-grants. Do I duplicate the information in my Grantee table into a Sub-Grantee table? Not a good idea. What we do is create an intersection table with foreign keys to an "Organization" table for grantor and grantee, plus either additional columns about the grant itself or foreign keys to that information.

KenDowns said...

John, the point of customers also being vendors is subtle, but clear. Customers and vendors are different sorts of business entities, mixing them together creates complications in the application code. Separating them prevents those complications. If this means a company is listed twice, this is usually easier to handle than debugging the code, especially since very often the contact information and relevant mailing addresses are different for each department. My argument is only that separate entities should be tracked separately, and to avoid the temptation to group them together because of occasional overlap.

With respect to your government example, I would need to know more before offering a conclusion, but based on what you have said there is no need for a table of "organizations", that table is a fictional abstraction. There is a table of "grantees" and everybody in it receives grants. The intersection table you mention records the facts of the subgrants when they happen. What you have here is a basic hierarchy of grants.

br1 said...

I can't help to think that repetition is always wrong. By having similar tables, the same information is repeated in the schema definition. One has to update both simultaneously to keep the union well typed. It seems table repetition is a road to exponential explosion.

KenDowns said...

Bruno: the trick is to realize that it is not repetition. Two tables may have a similar list of columns, but that hardly means they are tracking the same entities. Salt and sugar look alike, should we store them together in a bowl? If two twins always wear the same clothes, are they only a single person?

But laying aside the theory, I can only offer my experience, which is that changing an occasional UNION is far less work than writing filter code to keep the salt and sugar separate after they've been mixed together.

Anonymous said...

The argument against putting all different information in different tables is that it will make a lot of tables. more tables are difficult to manage, difficult to remember. difficult to import export. I am working on a inventory/income/expense system of pharmacy where we have one master and on detail table and we are putting all sorts of information like sales, purchases, returns, other income, expenses in one master and one detail table, and we identify different information using a TYPE column. I am very comfortable developing forms and reports with these two tables. i have created all sorts of reports like ledgers, trial balances, cash flows, profit and loos, and i only need to remember two tables.
Fahd

angeleena said...

Freelance Certified Wireless Security Engineer

Abbie Wright said...

Do you need help with essay writing assignment? Our assignment help experts are always ready to assist. Writers at GoAssignmentHelp.com are completely aware of all kinds of essay writing such as logistics management assignment help, reflective essay, argumentative essay, and much more. Our writers curate every case study assignment by following the guidelines and standards given by the professors. Thus, with us, students can expect to get the accurate assignment within the deadline. Moreover, we ensure that each of our assignment is completely unique and contains in-depth Assignment help topics. GoAssignmentHelp.com are highly committed to providing you with unique and plagiarism free content. If you have a dream to score high in assignments and want to fulfil your academic goals, then we are the perfect match for you. We have helped several students all over world to get rid of the stress of assignment helper. For more details, you can contact our customer care support team who will guide on the needful concerns.

jeewangarg said...

ppc services in Delhi We are the best ppc services in Delhi and we make sure that every penny you spend fetches you positive results. Pay per click (PPC) is a paid advertising model that comes under search engine marketing (SEM). With PPC, the advertiser only pays when people interact through impressions or clicks.

Kevin Johnson said...

On the off chance that this implies an organization is recorded twice, this is typically simpler to deal with than troubleshooting resolve by pay someone to take my online course, particularly since regularly the contact data and significant street numbers are distinctive for every division.

phautecouture said...

We aim to reach our customers worldwide, providing the latest and the best Indian Bridal Dresses, Wedding Indian Dress, indian wedding dresses online, and more.

Anonymous said...

To effectively manage the lead management process, you need the best customer relationship management tool. SalezShark CRM software revolutionized the sales process with its high-tech sales automation tools.

prakash tradewell said...

Prakash Tradewell, with over decades of experience in manufacturing and supplying electrical insulating sleeves and cables, has boarded on new industry benchmarks. Our range includes the best fiber sleeves, heat-shrinkable sleeves, fiberglass sleeve, insulating wires, fiber glass manufacturer. We also offer polyester yarns and polyester threads. Prakash Tradewell’s procure high-quality raw material from our reliable vendors for the manufacturing of these products.

Rahul Saxena said...

Divorce and Family Law Solicitors in London .
Immigration Solicitors in London
Settlement Agreement In London
Best solicitor in London
Child contact order experts in london
Transfer of Property in London
Uk spouse visa in london

Vipul Singh said...

Nice one, keep producing the type of content.
ENT Specialist in Meerut
CBSE Affiliated School in Meerut
Hair Transplant Doctor in Meerut
software development company in meerut
Jaina Jewellers Meerut
SEO Trainer in Meerut
Settlement Agreement In London
Best Digital Marketing Company in Meerut

magosucom77 said...

Thanks for posting this, it was unbelievably informative and helped me a lot. Well-written blog post.Click Here 경마사이트


3YAnGsKIE

toto365prokt said...

There is definately a great deal to learn about this subject. I really like all of the points you have made.| 토토사이트

toto365prokt said...

온라인카지노 Hello there! Quick question that’s totally off topic. Do you know how to make your site mobile friendly? My website looks weird when viewing from my iphone4. I’m trying to find a template or plugin that might be able to resolve this issue. If you have any recommendations, please share. Thanks!

카지노사이트 said...

which paid her family $650,000, required the jail to remove suicide hazards by the end of 2019 and to stop housing inmates under 18 with adults.
https://www.yert200.com

카지노사이트 said...

pleaded no contest Tuesday in Brown County Circuit Court to charges including child neglect resulting in death, hiding a child's corpse and neglect
https://www.shine900.com

카지노사이트 said...

The most serious offense, child neglect resulting in death, carries a maximum sentence of 25 years in prison.
https://www.gain777.com

카지노사이트 said...

An investigation began after a neighbor reported seeing Hussein's other two children outside unsupervised. Investigators discovered his body in a duffel bag in the trunk of Hussein's car in March 2020. The boy was mummified and significantly undernourished, according to the medical examiner.
https://www.kkr789.com

카지노사이트 said...

A lawyer shot his client to death in a courtroom in northern Mexico Wednesday, than fled into an adjoining chamber and killed himself with a gunshot to the head.
https://www.aace777.com

카지노사이트 said...

"The Nordic hamstring curl is one of the best bang-for-your-buck hamstring exercises out there," Men's Health fitness director
https://www.qqueen700.com

카지노사이트 said...

wanted to get better at them. Curtis notes that the Nordic Curl was first outlined in a publication back in 1880, and it's a move that continues to challenge leg strength.
https://www.rcasinosite.com

카지노사이트 said...

"Why not try and improve some of my weakness and train it everyday for an entire month," he says. To do so, he went to the hardware store for a plank of wood and some bolts to construct his own piece of equipment (which mimics a curl bar).
https://www.hotgame8.com

bacarasite said...

Took me time to read all the comments, but I genuinely enjoyed the article. It proved to become extremely beneficial to me and I am positive to all of the commenters here! 온라인카지노

powerballsitecom said...

I appreciate you taking time to share such valuable information. I had no clue on some of the things you mentioned earlier, thanks! 파워볼게임

casinositelink said...

Of course, what a fantastic blog and instructive posts, I definitely will bookmark your blog. All the Best! 카지노

메가슬롯 said...

I think your writing will help me, can you come to me once and help? My site is "메가슬롯


먹튀검증업체 said...

When I read your article on this topic, the first thought seems profound and difficult. There is also a bulletin board for discussion of articles and photos similar to this topic on my site, but I would like to visit once when I have time to discuss this topic. 먹튀검증업체I think it would be nice if you come to if you can solve my problem.


sòng bạc said...

Hello, I am one of the most impressed people in your article. sòng bạc I'm very curious about how you write such a good article. Are you an expert on this subject? I think so. Thank you again for allowing me to read these posts, and have a nice day today. Thank you.


오공슬롯 said...

It's the same topic , but I was quite surprised to see the opinions I didn't think of. My blog also has articles on these topics, so I look forward to your visit.오공슬롯


안전놀이터 said...

How can you think of this? I thought about this, but I couldn't solve it as well as you.안전놀이터I am so amazing and cool you are. I think you will help me. I hope you can help me.


온라인바카라 said...

Hello, I am one of the most impressed people in your article. 온라인바카라 If possible, please visit my website as well. Thank you.


바카라사이트 said...

Your writing is perfect and complete. 바카라사이트 However, I think it will be more wonderful if your post includes additional topics that I am thinking of. I have a lot of posts on my site similar to your topic. Would you like to visit once?


사설토토사이트 said...

I’m very pleased to discover this site. I want to to thank you for ones time for this particularly wonderful read!! I definitely savored every part of it and i also have you saved as a favorite to see new information on your blog. 사설토토사이트


keo nhacai said...

That's a great article! The neatly organized content is good to see. Can I quote a blog and write it on my blog? My blog has a variety of communities including these articles. Would you like to visit me later? keo nhacai


온라인바카라 said...

Many thanks for the article, I have a lot of spray lining knowledge but always learn something new. Keep up the good work and thank you again. 온라인바카라


casino trực tuyến said...

This is the perfect post.casino trực tuyến It helped me a lot. If you have time, I hope you come to my site and share your opinions. Have a nice day.

mukoff said...

토토사이트 "Nobody approaches the race saying it's going to end in a crash," said Horner. "Our aim is to win on the track."

토토커뮤니티 said...

토토커뮤니티 "We've still got 24 hours or 23 hours (before qualifying) to try and find a better set-up and a bit more pace."

먹튀검증커뮤니티 said...

먹튀검증커뮤니티 He added: "It's almost like Squid Game (the popular Netflix series), the fact that we've arrived here level."

보증업체 said...

보증업체 "We're still learning and understanding a few things," said the 24-year-old Verstappen.

먹튀폴리스 said...

먹튀폴리스 "May the best man and the best team win," added Wolff, with Mercedes and Red Bull also fighting it out for the constructors championship.

슈어맨 said...

슈어맨 "And as I said I think we made some positive steps set-up wise, so we'll try and perfect it tonight and come back hard tomorrow."

keonha cai said...

I accidentally searched and visited your site. I still saw several posts during my visit, but the text was neat and readable. I will quote this post and post it on my blog. Would you like to visit my blog later? keonha cai


Assignment help Hong Kong said...

Now, you should not worry about his purpose and last your query with us. The professional team assignment help Hongkong available with us is to provide the best solution. Now, nobody can stop you to access a better grade. To know more information, you can surf our web address.

ayan said...

I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it.. 토토사이트

ayan said...

I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts. 바카라사이트

safe playgrounds 24/7 said...

토토사이트 I have been browsing online greater than three hours lately, yet I by no means discovered any interesting article like yours. It is pretty price sufficient for me. In my opinion, if all website owners and bloggers made good content material as you probably did, the internet will be a lot more useful than ever before.

safe playgrounds 24/7 said...

Nice post. I learn something totally new and challenging on websites I stumbleupon everyday. It’s always useful to read content from other authors and use a little something from other websites. | 온라인카지노

VISIT US said...

It’s remarkable to go to see this web site and reading the views of all mates about this paragraph, while I am also eager of getting know-how.

야동
립카페
타이마사지
안마
온라인카지노

jack chein aaaaa said...

i will probably be coming back to your weblog for greater quickly 토토커뮤니티

badugisitenet said...

This article is very detailed And i am happy reading this. 온라인바둑이

casinositehot.com said...

I think this is a real great blog article. enjoy reading this! 온라인카지노

Jackson mona said...

excellent to be visiting your weblog another time, it has been months for me. Well this text that ive been waited for therefore lengthy. I need this article to complete my challenge in the faculty, and has identical subject matter together with your article. Thank you, pleasant proportion. I just stumbled upon your weblog and desired to say that i've honestly loved studying your blog posts. Any way i'll be subscribing on your feed and that i hope you post once more quickly. Huge thanks for the beneficial info. In the pretty current past. The revel in changed into absolutely dazzling. At the off risk that lone i've the opportunity 먹튀검증커뮤니티

ddssddss said...

this is an fantastic motivating article. I'm nearly happy with your splendid paintings. You positioned simply extraordinarily supportive statistics. Maintain it up. Maintain running a blog. Hoping to perusing your next post . Truely appreciate this awesome put up that you have provided for us. Terrific site and a extremely good topic as well i clearly get amazed to read this. Its actually accurate. Without fail, your writing fashion is top professional; even your internet site also seems extraordinary thank you for posting. Desirable publish. I analyze some thing absolutely new and challenging on websites i encounter regular. 슈어맨주소

asddasdd said...

after i originally left a remark i seem to have clicked at the -notify me when new remarks are added- checkbox and to any extent further each time a remark is brought i receive four emails with the equal comment. Perhaps there's an easy technique you're capable of eliminate me from that carrier? Many thank you! Greetings! Very beneficial recommendation in this precise article! It is the little adjustments with a purpose to make the most critical changes. Many thanks for sharing! You are so exciting! I do not suppose i have examine a unmarried factor like this before. So awesome to find a person with a few original thoughts in this problem. Actually.. Thanks for starting this up. This website is some thing it is wished on the net, someone with a piece of originality! It­s hard to return via educated humans approximately this situation, but, you appear to be you already know what youíre talking about! Thank you 사설토토

jack chein aaaaa said...

I don t have the time at the moment to fully read your site but I have bookmarked it and also add your RSS feeds. I will be back in a day or two. thanks for a great site. 안전놀이터

Jackson mona said...

howdy! This newsletter could not be written much higher! Searching through this text rings a bell in my memory of my preceding roommate! He constantly stored preaching approximately this. I am going to send this newsletter to him. Fairly certain he will have a remarkable study. Thank you for sharing! Hi there, i think your weblog can be having browser compatibility problems. When i check your weblog in safari, it looks nice but when starting in i. E., it has some overlapping troubles. I just desired to offer you a short heads up! Except that, first-rate website online! Spot on with this write-up, i genuinely sense this terrific web site needs a tremendous deal extra attention. I’ll possibly be returning to study through extra, thanks for the records! Aw, this become a clearly top put up. Taking the time and real attempt to create a very good article… but what can i say… i positioned matters off a whole lot and do not manage to get nearly something carried out. 토토사이트

Robert said...

What I don't grasped is altogether you're by and by not entirely more particularly liked than you may be at present. You're particularly clever. You comprehend thusly broadly with respect to this subject, made me to the extent it is important for me imagine it from such innumerable various focuses. Its like individuals are excluded aside from on the off chance that it is one thing to do with Girl insane! Your individual stuffs unbelievable. Constantly handle it up! You are particularly valued than you might be right now. 모두의토토

jack chein aaaaa said...

Listed here you'll learn it is important homestay melaka

jack chein aaaaa said...

Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. I can't wait to read lots of your posts 토토커뮤니티

jack chein aaaaa said...

"Just Browsing While I was browsing yesterday I noticed a excellent post concerning

" 파워볼메이저사이트

John Smith said...

The way you explain in this post is outstanding and knowledgeable.
Again thanks for sharing such an informative post. I will share this
information on my social media platforms which are really helpful for others.
Read similar post related to residential relocation services

SEO CANADA said...

Thanks for providing recent updates regarding the concern, I am looking to read more. bitmain antminer L7

Assignment help said...

Nice post! It is really interesting and wonderful. Thanks for sharing. Hire a professional and experienced writer and get International Finance Assignment Help at affordable rates and within the deadline.

Buygenmeds said...

Excellent and beautiful information post. thank you for sharing this information.
If you have time please visit my website.
Buygenmeds is a global medicine provider with a focus on ED Medicine Expert at Buygenmeds- The Trusted Pharmacy Store.
like, Cenforce 100 is a medication used to treat impotence and erectile dysfunction (ED).

토토사이트 said...

For the first time in his Tottenham Hotspur career, South Korean star Son Heung-min will represent his Premier League club on his home soil. And he wants to make the appearance a memorable one. 토토사이트

토토커뮤니티 said...

Though Wednesday's contest is an exhibition match, Son insisted he wants to kick off the preseason on the right note. 토토커뮤니티

메이저놀이터 said...

Football fans, hardcore or casual types alike, hang on Son's every word and every move, even in training. 메이저놀이터

슬롯커뮤니티 said...

Morimando had been pitching in for the CTBC Brothers in the Chinese Professional Baseball League in Taiwan this season, posting a 7-5 record and a 2.56 ERA in 15 appearances. 슬롯커뮤니티

ravensky867 said...

My First Look at Lightning 온라인포커 스윗포인트 on Hollywoodbets

Dinstar India said...

Dinstar FXO Gateway support 4, 8 and 16 FXO ports, connect traditional telecom operator phone lines or analog PBXs to IP telephony networks.

Smile Surgeon Clinic said...

Looking for the Best Dental Clinic in Faridabad? If Yes! Then The Smile Surgeon is the best choice for Best Orthodontist in Faridabad. We are offering all types of dental treatment at a very reasonable cost.
WhatsApp Number: 9315716954
visit us: https://thesmilesurgeon.in

Anonymous said...

나는 이것이 유익한 게시물이라고 생각하며 매우 유용하고 지식이 풍부합니다. 따라서이 기사를 작성하는 데 많은 노력을 기울여 주셔서 감사합니다. 링크모음

Anonymous said...

흥미로운 블로그는 여기에 쓰여진 사실과 정보가 정확합니다. 블랙 가죽 오토바이 재킷 스포츠중계

Elsaroy roy said...

Fildena 100mg is a readily available medication for treating erectile dysfunction, or the inability to produce a satisfying penile erection for sexual performance. The effects of the medicine are due to the active component sildenafil.
Sildenafil, a common element in many other well-known ED medications, is the active component of the well-known "little purple pill." Nitric oxide is the main component of PD5 inhibitors, which improve blood flow to the penis. As a reminder, several sports supplements include nitric oxide-boosting components that may interfere with one another.
Fildena is offered in 25 to 50 mg and 100 mg tablet and gel capsule forms. The recommended dosage for Fildena orVilitra 20 mg once daily, between 30 and 4 hours prior to sexual activity. Even 25mg is too low for some folks to have great benefits. Not often does more mean better. There is no evidence of a positive impact at greater doses, and the risk of negative effects increases.

Sophia Johnson said...

Before initiating Cenforce 200mg or any other treatment for erectile dysfunction, it is essential to consult with a healthcare professional. They will assess your medical history, evaluate potential drug interactions, and provide personalized guidance for safe and effective usage. Additionally, they can address any concerns or questions you may have regarding Cenforce 200mg.
Read More: Buy cenforce 100mg

python ai programming said...

Good information.