使用什么连接?

发布于 2024-11-10 16:36:39 字数 1553 浏览 2 评论 0原文

我有 2 个不同的表,其中只有一个具有相同名称(“用户名”)的字段。他们彼此没有关系。我只需要一个查询来选择其中该字段等于给定值的所有行。

我想出了这个,这当然是错误的...... SELECT * FROM user AS a FULL JOIN future_user AS b WHERE a.username=x OR b.username=x

我正在谈论这些表:

CREATE TABLE user
(
uid      mediumint(6) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,

username varchar(15) NOT NULL,
password varchar(15) BINARY NOT NULL,
mail     varchar(50) NOT NULL,

name     varchar(50) NOT NULL,
surname  varchar(50) NOT NULL,
birth    char(10) NOT NULL,
sex      tinyint(1) unsigned NOT NULL default 1,

address  varchar(50) NOT NULL,
city     varchar(50) NOT NULL,
zip      char(5) NOT NULL,
province varchar(50) NOT NULL,
country  tinyint(3) NOT NULL,

number1  varchar(50) NOT NULL,
number2  varchar(50) NOT NULL,

last_login   TIMESTAMP,    
registered   TIMESTAMP,
online       tinyint(1) unsigned default 0,

admin           tinyint(1) unsigned default 0,
comment_allowed tinyint(1) unsigned default 0,
post_allowed    tinyint(1) unsigned default 0

) ENGINE=InnoDB;

CREATE TABLE future_user
(
username varchar(15) NOT NULL,
password varchar(15) BINARY NOT NULL,
mail     varchar(50) NOT NULL,

name     varchar(50) NOT NULL,
surname  varchar(50) NOT NULL,
birth    char(8) NOT NULL,
sex      tinyint(1) unsigned NOT NULL,

address  varchar(50) NOT NULL,
city     varchar(50) NOT NULL,
zip      char(10) NOT NULL,
province varchar(50) NOT NULL,
country  varchar(50) NOT NULL,

number1  varchar(50) NOT NULL,
number2  varchar(50) NOT NULL,

code     char(10) NOT NULL

) ENGINE=InnoDB;

I have 2 different tables, which have just one field with same name ('username'). They're not related each other. I need with just one query to select all the rows among them both which have this field equal to a given value.

I came up with this, which is off course wrong...
SELECT * FROM user AS a FULL JOIN future_user AS b WHERE a.username=x OR b.username=x

These the tables I'm talking about:

CREATE TABLE user
(
uid      mediumint(6) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,

username varchar(15) NOT NULL,
password varchar(15) BINARY NOT NULL,
mail     varchar(50) NOT NULL,

name     varchar(50) NOT NULL,
surname  varchar(50) NOT NULL,
birth    char(10) NOT NULL,
sex      tinyint(1) unsigned NOT NULL default 1,

address  varchar(50) NOT NULL,
city     varchar(50) NOT NULL,
zip      char(5) NOT NULL,
province varchar(50) NOT NULL,
country  tinyint(3) NOT NULL,

number1  varchar(50) NOT NULL,
number2  varchar(50) NOT NULL,

last_login   TIMESTAMP,    
registered   TIMESTAMP,
online       tinyint(1) unsigned default 0,

admin           tinyint(1) unsigned default 0,
comment_allowed tinyint(1) unsigned default 0,
post_allowed    tinyint(1) unsigned default 0

) ENGINE=InnoDB;

CREATE TABLE future_user
(
username varchar(15) NOT NULL,
password varchar(15) BINARY NOT NULL,
mail     varchar(50) NOT NULL,

name     varchar(50) NOT NULL,
surname  varchar(50) NOT NULL,
birth    char(8) NOT NULL,
sex      tinyint(1) unsigned NOT NULL,

address  varchar(50) NOT NULL,
city     varchar(50) NOT NULL,
zip      char(10) NOT NULL,
province varchar(50) NOT NULL,
country  varchar(50) NOT NULL,

number1  varchar(50) NOT NULL,
number2  varchar(50) NOT NULL,

code     char(10) NOT NULL

) ENGINE=InnoDB;

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

兔姬 2024-11-17 16:36:39

使用 UNION

SELECT
    fields
FROM
    user
WHERE
    user.username=x
UNION
SELECT
    fields
FROM
    future_user
WHERE
    future_user.username=x

请注意,您不能在其中执行 SELECT *,因为它们具有不同的字段。您需要从两个子查询返回相同的字段。

Use a UNION:

SELECT
    fields
FROM
    user
WHERE
    user.username=x
UNION
SELECT
    fields
FROM
    future_user
WHERE
    future_user.username=x

Note that you can't do SELECT * in either because they have different fields. You'll need to return the same fields from both subqueries.

鹤仙姿 2024-11-17 16:36:39

听起来您正在寻找的是完全外连接。某些数据库系统(尤其是 MySQL)不支持此类连接。对于那些这样做的人,您的查询将类似于:

SELECT * FROM user FULL OUTER JOIN future_user
  ON user.username = future_user.username;

您将必须使用 LEFT JOIN 和 RIGHT JOIN 的 UNION,如上面链接的维基百科文章中所述,类似于(在 MySQL 中):

SELECT * FROM user LEFT JOIN future_user
  ON user.username = future_user.username
UNION
SELECT * FROM user RIGHT JOIN future_user
  ON user.username = future_user.username;

对于那些不这样做的人, 可能需要认真考虑是否有可用的替代方案,例如组合表或处理后端两个单独查询的结果集。 FULL OUTER JOIN 使用起来非常奇怪,您最终会得到一堆必须检查和验证的 NULL 字段值。

It sounds like what you are looking for is a Full Outer Join. Some database systems (notably MySQL) do not support such joins. For those that do, your query would be something like:

SELECT * FROM user FULL OUTER JOIN future_user
  ON user.username = future_user.username;

For those that don't, you will have to use a UNION of a LEFT JOIN and RIGHT JOIN as explained in the Wikipedia article linked above, something like (in MySQL):

SELECT * FROM user LEFT JOIN future_user
  ON user.username = future_user.username
UNION
SELECT * FROM user RIGHT JOIN future_user
  ON user.username = future_user.username;

You may want to serious consider if there is an alternative available, such as combining the tables or processing the resultsets from two separate queries on the back end. FULL OUTER JOINs are very weird to be using, and you're going to end up with a bunch of NULL field values that you'll have to check for and validate.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文