MySQL从两个表中选择 [英] Mysql selecting from two tables

问题描述

有人可以告诉我如何从两个表中选择数据，而不必使用联接吗?

类似这样的东西:

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

澄清

我有这两个表，它们具有相同的字段. IE:table1包含2011年的数据，而table2包含2012年的数据.我想全部获取它们.

进一步说明:

所需结果集可以通过以下方式产生:

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

并且OP希望查看是否还有其他方法可以加快此速度(我尝试在这些查询之间使用UNION.但是查询速度花了0.1887秒.这有点慢.")

(@ Jetoox:如果这不是您的意图，请编辑您的问题并加以澄清).

解决方案

只需将连接条件放在WHERE子句中即可:

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

那是一个内部联接.

更新

在查看查询时:在这种情况下，tbl_transactions和tbl_transactions_bk_2012之间没有任何关系(即，在person_key上将它们连接起来是没有意义的，因为两个表之间的关系不存在(例如) tbl_transactions与人有关).

然后，您应该使用UNION方法.尝试使用JOIN或FROM xx, yy WHERE xx.id=yy.id将第一个查询连接到第二个查询是没有意义的，并且不会为您提供所需的结果.

顺便说一句，将来将您当前的查询/尝试放在您的帖子中-如您所见，它将阻止您获得不适合您的问题的答案(就像我的第一次尝试一样.)

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

解决方案

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

这篇关于MySQL从两个表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！