MySQL问题:在空表上的LEFT JOIN [英] MySQL issue: LEFT JOIN on empty table

问题描述

给定一个具有两个表X和Y的数据库，我有一个查询，该查询应该对属性X.a1和Y.b1上的两个表进行LEFT JOIN.我使用以下查询:

Given a database with two tables X and Y, I have a query that should LEFT JOIN the two tables on attributes X.a1 and Y.b1. I used the following query:

SELECT X.a1, X.a2, Y.b1, Y.b2 FROM X LEFT JOIN Y ON (X.a1 = Y.b1)

我认为即使Y当前是一个空表，它也足以正常工作.但是，查询中断是因为表Y为空.有什么方法可以重新格式化此查询，以便即使Y是一个空表，LEFT JOIN也不会中断?还是我只需要始终确保表Y中有一些数据，即使它与表X中的任何内容都不匹配(因此与LEFT JOIN无关).

I thought that would be good enough to work, even if Y is currently an empty table. However, the query breaks because table Y is empty, it seems. Is there any way to reformat this query so that even if Y is an empty table, the LEFT JOIN will not break? Or do I just need to always make sure that there is some data in table Y, even if it doesn't match anything in table X (hence the LEFT JOIN).

推荐答案

您的表名有些混乱.是X和Y，还是X.a和Y.b?

Your table names are a little confusing. Is it X and Y, or X.a and Y.b?

如果X和Y:

SELECT X.a1, X.a2, Y.a1, Y.b2 FROM X LEFT OUTER JOIN Y ON (X.a1 = Y.b1)

应带回所有X，对于没有匹配记录的Y.a1和Y.b2为空.

should bring back all X, with nulls for the Y.a1 and Y.b2 where there is no matching record.

这篇关于MySQL问题:在空表上的LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！