UNION

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

UNION

Post by jayson.ph »

Hi, All-->

i have 4 tables that i need to UNION somethink like below:

Code: Select all

 SELECT * FROM table1 UNION SELECT table2.field1, table2.field2 FROM table2 INNER JOIN table3 on table2 INNER JOINT table4 on table3;
somehow i get what i want, but not exactly correct. otherwise i need clarification for the query. what is the possible to get wrong output field or somechange.

see image below: somewhere change in item code and item category.
Image

Image
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: UNION

Post by tr0gd0rr »

You may want to use UNION and specify the column names explicitly:

Code: Select all

SELECT field1, field2, field3 FROM table_a
UNION
SELECT equivalent_field1, equivalent_field2, equivalent_field3 FROM table_b
UNION
SELECT equivalent_field1, equivalent_field2, equivalent_field3 FROM table_c
UNION
SELECT equivalent_field1, equivalent_field2, equivalent_field3 FROM table_d
That way it will work regardless of the order of the columns in the database.
If you are using JOINS, you will end up with results with 4x as many columns, not a single set of results from all 4 tables. So you probably want UNION not JOIN. Keep in mind that the performance of UNION is pretty poor, especially if you are using ORDER BY.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: UNION

Post by requinix »

My question is why there are three (or more) tables with the same structures. Wouldn't they work best as one table?
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: UNION

Post by tr0gd0rr »

If you are ever going to use JOIN or UNION on the tables yes. But sometimes it is best to have multiple tables with the same schema. For example, maybe you have a table with blog post images and another with user avatar images. If you are never going to JOIN or UNION those tables, having two tables will make queries faster.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: UNION

Post by requinix »

tr0gd0rr wrote:If you are ever going to use JOIN or UNION on the tables yes. But sometimes it is best to have multiple tables with the same schema. For example, maybe you have a table with blog post images and another with user avatar images. If you are never going to JOIN or UNION those tables, having two tables will make queries faster.
And the answer to my question would then be "because they hold different things". Which is a perfectly acceptable answer. My point is that there are also lots of other, less acceptable answers.
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: UNION

Post by tr0gd0rr »

requinix wrote:"because they hold different things"
Exactly!
Post Reply