Page 1 of 1

UNION

Posted: Thu Aug 02, 2012 5:35 am
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

Re: UNION

Posted: Thu Aug 02, 2012 2:10 pm
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.

Re: UNION

Posted: Thu Aug 02, 2012 4:53 pm
by requinix
My question is why there are three (or more) tables with the same structures. Wouldn't they work best as one table?

Re: UNION

Posted: Fri Aug 03, 2012 2:17 pm
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.

Re: UNION

Posted: Fri Aug 03, 2012 8:47 pm
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.

Re: UNION

Posted: Sat Aug 04, 2012 5:21 pm
by tr0gd0rr
requinix wrote:"because they hold different things"
Exactly!