CODEWITHSUNDEEP
mysqlsql
Jetoox
Jetoox

Reputation: 1437

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).

Upvotes: 17

Views: 97547

Answers (5)

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799450

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 = ''

Upvotes: 12

jyotiprakash
jyotiprakash

Reputation: 2086

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

Upvotes: 2

Stephen Senkomago Musoke
Stephen Senkomago Musoke

Reputation: 3523

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

Upvotes: 0

mathematical.coffee
mathematical.coffee

Reputation: 56935

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).

Upvotes: 29

silly
silly

Reputation: 7887

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

Upvotes: 4

Related Questions