Mysql selecting from two tables

Can anybody tell me how to select data from two tables, without having to use join?

Something like this:

SELECT t1.*, 
       t2.*
  FROM table1 t1, 
       table2 t2

###Clarification
I have these two tables, that have the same fields. IE: table1 contains data from 2011 and table2 contains data in 2012. I want to get them all.

###Further clarification:
The result set desired can be produced by:

(SELECT tr.full_name, tr.headlines, tr.content, tr.stamp, tr.person_key
 FROM tbl_transactions tr
          JOIN persons p ON p.person_key = tr.person_key
          JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams')
 WHERE t.team_key = '')
UNION
(SELECT tr.full_name, tr.headlines, tr.content, tr.stamp, tr.person_key
 FROM tbl_transactions_bk_2012 tr
          JOIN persons p ON p.person_key = tr.person_key
          JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams')
 WHERE t.team_key = ''

and the OP wishes to see if there are alternative ways to speed this up (“I tried to use UNION in between those queries. but query speed took 0.1887 secs. it’s kinda slow.”)

(@Jetoox: if this is not your intent, please edit your question and clarify).

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Just put the join condition in the WHERE clause:

SELECT t1.*, t2.*
FROM table1 t1, table2 t2
WHERE t1.id = t2.t1_id

That is an inner join, though.

UPDATE

Upon looking at your queries: In this particular case, there is no relation between tbl_transactions and tbl_transactions_bk_2012 (i.e. joining these on person_key is meaningless because there is no relationship between the two tables in the way that (say) tbl_transactions and persons are related).

Then, you should use the UNION approach. Trying to join the first query to the second using either JOIN or FROM xx, yy WHERE xx.id=yy.id is meaningless and won’t give you the results you need.

By the way, in the future, put your current query/attempt in your post – as you can see it will prevent you from getting answers that aren’t appropriate for your question (as my first attempt was).

Method 2

You want UNION.

(SELECT tr.full_name, tr.headlines, tr.content, tr.stamp, tr.person_key
 FROM tbl_transactions tr
          JOIN persons p ON p.person_key = tr.person_key
          JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams')
 WHERE t.team_key = '')
UNION
(SELECT tr.full_name, tr.headlines, tr.content, tr.stamp, tr.person_key
 FROM tbl_transactions_bk_2012 tr
          JOIN persons p ON p.person_key = tr.person_key
          JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams')
 WHERE t.team_key = ''

Method 3

select t1.*, t2.* from table1 t1, table2 t2
where t1.fkey = t2.pkey

Method 4

select t1.* , t2.*
from t1, t2 where t1.id=t2.id;

Method 5

While using the UNION query, you may also want to add indexes to any columns that you are using to join and filter which will improve performance

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating