使用连接显示空行

发布于 2024-08-25 22:03:19 字数 886 浏览 3 评论 0原文

在 mysql 中,我从表中选择大喊 具有另一个名为“roleuser”的表的外键 匹配列为 user_id

现在,shouts 表中某些行的 user_id 列为空 (实际上不是空的,但在mysql中没有插入)

如何显示shouts表的所有行,无论user_id是否为空,

我正在执行sql语句,

SELECT s.*, r.firstname, r.lastname
FROM shouts s left join roleuser r where r.user_id = s.user_id limit 50;

该语句不执行并显示,

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where r.user_id = s.user_id limit 50' at line 2

但使用内部连接,sql执行显示行 其中,shouts 表中只有 user_id 值。不显示空值。

SELECT s.*, r.firstname, r.lastname
FROM shouts s inner join roleuser r where r.user_id = s.user_id limit 50;

我怎样才能显示喊话表中的所有行和空值 在shouts 表中,user_id 为空的名字和姓氏列。 如果根本不可能使用 sql 可能会使用存储过程...

谢谢

Pradyut

In mysql i m selecting from a table shouts
having a foreign key to another table named "roleuser"
with the matching column as user_id

Now the user_id column in the shouts table for some rows is null
(not actually null but with no inserts in mysql)

How to show all the rows of the shouts table either with user_id null or not

I m executing the sql statement

SELECT s.*, r.firstname, r.lastname
FROM shouts s left join roleuser r where r.user_id = s.user_id limit 50;

which does not executes and shows

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where r.user_id = s.user_id limit 50' at line 2

but using inner join the sql executes which shows rows
which only have user_id values in the shouts table. the nulls are not shown.

SELECT s.*, r.firstname, r.lastname
FROM shouts s inner join roleuser r where r.user_id = s.user_id limit 50;

How can i show all the rows from the shouts table and null values in the
firstname and lastname columns where the user_id is null in the shouts table.
If not at all possible with sql may be using stored procedures...

Thanks

Pradyut

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

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

发布评论

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

评论(2

待"谢繁草 2024-09-01 22:03:19

您需要在连接中使用 ON 子句。

SELECT s.*, r.firstname, r.lastname
FROM  roleuser r 
  LEFT JOIN shouts s 
    ON s.user_id = r.user_id 
WHERE
    r.firstname like '%byron%' -- where clause goes here..

LIMIT 50;

You want an ON clause in your join.

SELECT s.*, r.firstname, r.lastname
FROM  roleuser r 
  LEFT JOIN shouts s 
    ON s.user_id = r.user_id 
WHERE
    r.firstname like '%byron%' -- where clause goes here..

LIMIT 50;
情场扛把子 2024-09-01 22:03:19

看起来您希望

SELECT s.*, r.firstname, r.lastname 
FROM shouts s left join roleuser r ON r.user_id = s.user_id limit 50;

您使用的是 WHERE 而不是 ON

您的第二个查询有效,因为

SELECT s.*, r.firstname, r.lastname 
FROM shouts s inner join roleuser r where r.user_id = s.user_id limit 50; 

恰好给出了相同的结果

SELECT s.*, r.firstname, r.lastname 
FROM shouts s inner join roleuser r ON r.user_id = s.user_id limit 50; 

尽管它们的含义略有不同,但 。对于 INNER JOIN,ON 是可选的,如果您将其保留,您将获得第一个表中的所有行和第二个表中的所有行的笛卡尔积,然后是您的 WHERE 子句过滤掉所有不匹配的行。如果您将条件放入 ON 子句中,则联接将仅计算匹配的行,并且您无需将条件放入 WHERE 子句中。无论哪种情况,内连接的结果都是相同的。

但是,外连接没有等效的 WHERE 子句,因此它的 ON 子句不是可选的。

It looks like you want

SELECT s.*, r.firstname, r.lastname 
FROM shouts s left join roleuser r ON r.user_id = s.user_id limit 50;

You were using WHERE instead of ON.

Your second query worked because

SELECT s.*, r.firstname, r.lastname 
FROM shouts s inner join roleuser r where r.user_id = s.user_id limit 50; 

just happens to give the same results as

SELECT s.*, r.firstname, r.lastname 
FROM shouts s inner join roleuser r ON r.user_id = s.user_id limit 50; 

even though they mean slightly different things. For an INNER JOIN, the ON is optional and if you leave it off you get the Cartesian product of all the rows in the first table and all the rows in the second table, then your WHERE clause filters out all the mismatched rows. If you put the condition in the ON clause instead, the join evaluates to only the matched rows and you don't need to put a condition in your WHERE clause. In either case the results are identical for an inner join.

However an outer join has no equivalent WHERE clause equivalent, so its ON clause is not optional.

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