Alternative to UNION operation

Posted on 2002-06-20
Last Modified: 2006-11-17
I'm using the latest stable  - and recommended - release of MySQL, the 3.23 version. What bugs me is that the UNION operation is only implemented in MySQL 4.0 (as per, while this one is still alpha. I'm not too sure about using it, especially just to accomodate ONE query. Are there any alternatives? Anything that might give the same result as UNION? Bascially, what I need is a query that merges two columns into one.

Question by:JfSmith
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1

Expert Comment

ID: 7096712
u can use left join.....only...there is no union adn subquery...

Author Comment

ID: 7096783
And how can I merge two columns into one using a left join?

Expert Comment

ID: 7096893
I'm sure we'd have to see more of what you're trying to do as a UNION, in order to recommend a substitution.

THe standard replacement for UNION in mySQL is to use a temporary table, and use each select in the union separately to insert into the temp.  Then select DISTINCT records (for standard UNION) to get the rows back.

That's a lot of work for the one query, though.  If you post more details, a more elegant answer might be available.

As for merging two columns, a variety of mySQL functions can test the value of a column in a result set, and replace it with another column:  ifnull, isnull, case, etc...

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


Author Comment

ID: 7096988
Kelfink, thanks for your reply.

What I'm doing is rather straightforward. Here I'll adapt and simplify things a little bit. Let's see. There's a table, called SOURCES, with various information about numerous published sources. It has two author fields, say author1 and author2, for each record. For the purpose of building a drop-down list containing ALL authors ever added in the whole table SOURCES, I need to merge all the unique entries of the fields author1 and author2 into a one-column query.

I actually used MSSQL and a UNION operator, and everything was fine. But now I have migrated to MySQL. Please convince me everything's fine with that. ;-)

By the way, for me it is important to do it that way, because the drop-down list is built dynamically. Maintaining a separate and static index of authors would work but would be too much pain.

Also, perhaps you can tell me what are the "risks" to use MySQL 4.0 alpha. This way I wouldn't have to readap my query.

Thanks a lot,

Expert Comment

ID: 7098910
The best thing would be a schema re-design.  But I don't know if that is an option...

Author Comment

ID: 7098952
Unfortunately I cannot re-design the structure of the table. The two author fields have to be kept separate for several reasons, but here especially because they must be listed *individually* in my drop-down list.

I really need a way to figure this one out... I repeat I COULD do that with MSSQL. I really didn't expect MySQL to disappoint me on that... unless I use MySQl 4.0. Can somebody tell me if that is risky or not?


Expert Comment

ID: 7098983
>Unfortunately I cannot re-design the structure of the
>table. The two author fields have to be kept separate for
>several reasons, but here especially because they must be
>listed *individually* in my drop-down list.

You can do that with a new schema...

I have not used MySQL 4, so can't recommend.  Is this for a web app?  Shouldn't be a problem to handle this in code rather than SQL...


Author Comment

ID: 7099028
Well, I can't shy away from my limited experience, so I have to ask what is a schema.

It is indeed for a webapp. I agree I could code something, but it seems so much out of proportions for just one query, and to replace ONE word at that. :-) I'm still under the shock that this detail turns out to be such an issue. I'd have to grab the results of two separate SELECT DISTINCT queries, and then analyse everything so that every entry gets listed only once. Ouch. Now our table has 4366 records, meant to merge with similar projects by other teams. It's meant to get bigger.


Expert Comment

ID: 7099054
>I'd have to grab the results of two separate SELECT
>DISTINCT queries, and then analyse everything so that
>every entry gets listed only once.

No, you would use kelfink's method (temp table), and give him the points...

The schema is the layout of your db - what tables, columns are there.  Your current schema is de-normalized, that is why you are having problems like this.

Instead of TWO author fields in the SOURCES table, have none.  Then create an AUTHORS table, and a SOURCES_AUTHORS table. SOURCES_AUTHORS would contain

1               27
1              995
2             1732
2             8457

So each author entry for a source gets a row in the SOURCES_AUTHORS table.  This has additional benefits, e.g., you can have as many authors for a source as you want.

That way you would just do

SELECT * FROM AUTHORS to get your list - make the table unique by definition.  Or if you can't guarantee uniqueness, you would do SELECT DISTINCT LAST_NAME, FIRST_NAME FROM AUTHORS

Author Comment

ID: 7099271
Oh, schema's a simple word after all. Thank you for that.
Well, this is a very good solution that I realize now to have actually applied elsewhere (I'm not the original designer of the database currently under my administration). But in this case it is a little more complicated. It's just the situation. I really don't need to go into details though, you'd be bored to death quickly. This particular MySQL table is part of a database that is in truth a web version of an original FileMaker Pro database, which is under the responsibility of another team. The two versions need to be kept consistent, and I cannot impose the schema you suggest, however efficient, to the FileMaker side of the project.

If you don't mind, I'll wait a little more and see if kelfink has some more insights.


Expert Comment

ID: 7099296

For a db this small, Access would work fine, and would let you use UNION.

Accepted Solution

kelfink earned 100 total points
ID: 7104953
Sorry for the delay.  Had a spot of time off.

Well, I was hoping that some form of join would accomplish what you want.  But without redesigning, I don't see it.
drittich's suggestion to redesign the schema is essentially a more organized method of accomplishing what I suggested.  Long term, his idea should be best for organization.  Short term, my TEMP table should get the job done, and minimize other changes necessary in the code.

If you're happy with the structure for now, and want to wait for stable 4.0 to support UNION (I'd avoid implementing the beta 4.0 outside of a test environment...)
then my TEMP solution is minimal work. Here's the steps, roughly...

1) beforehand, create table TEMPAUTH ( authid, name );
2) INSERT INTO TEMPAUTH (authid, name) SELECT authid1, name1 from BOOKS;
3) INSERT INTO TEMPAUTH (authid, name) SELECT authid2, name2 from BOOKS;

Those steps assume you've got all your author data in the book table.  I'm guessing if you had a author table we wouldn't be having his discussion?

In a more pure world, you'd have one author table, which you'd join to the books table.  The author table could contain all kinds of wonderful information about the author, so you can keep that information in one place, easily modified... current phone number, address, university, etc..  That's normalization.  It's great.


Author Comment

ID: 7107585
Hehe, I understand. The short-term solution seems best for now. Thanks a lot!


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql between clause 2 41
MySQL Memory Keeps Increasing 4 63
MySql Recovery 2 34
Using Modal's in to Retrieve Data from MySql and Populate Forms 13 78
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question