Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select from multiple tables without a join?

Tags:

mysql

What is the easiest way to select data from two tables and rather than join them, have them appear as separate rows. Both tables have similar or matching fields and I want to run some aggregate function on them such as avg all the rows that occurred in the same month, from both tables.

for example I have two tables, one that is shows transactions from one system and another with transactions from a different system. Is there a way to grab all the transactions from both tables as separate rows? if table 1 had twenty records and table 2 have thirty records, I'd like there to be 50 rows on the return.

like image 908
Nicholas Hazen Avatar asked Aug 23 '12 15:08

Nicholas Hazen


People also ask

Can we select from multiple tables without join?

The UNION ALL operator may be what you are looking for. With this operator, you can concatenate the resultsets from multiple queries together, preserving all of the rows from each. Note that a UNION operator (without the ALL keyword) will eliminate any "duplicate" rows which exist in the resultset.

Can you select from multiple tables?

In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables.


1 Answers

You could try something like this:

SELECT ... FROM (     SELECT f1,f2,f3 FROM table1     UNION     SELECT f1,f2,f3 FROM table2 ) WHERE ... 
like image 175
Marco Avatar answered Sep 24 '22 21:09

Marco