SQL中AND与WHERE子句的用法

问题描述：

我正在研究与Oracle连接的PHP page.我遇到了此SQL，但不确定它是否在执行应做的工作，因此我想在这里提出.有问题的SQL是这样的:

I am working on PHP page that connects with Oracle. I came across this SQL and I am not sure it is doing what is supposed to, so I thought I would ask here. The SQL in question is like this:

select tableA.id, tableA.name, tableB.details 
from tableA
left join tableB on
tableB.id = tableA.id
and 
tableB.logId = '<logged_in_user>'

现在，当我以在tableB中没有条目的用户身份登录时，此查询运行时我仍在获取记录.我的假设是该子句应该是'where'而不是'and'.

Now when I log in as a user who does not have entries in tableB, I am still getting records when this query runs. And my hypothesis is that instead of 'and' the clause should have been 'where'.

left join tableB on
    tableB.id = tableA.id
    where
    tableB.logId = '<logged_in_user>'

所以我有两个问题.

我说得对吗?
如果是，那么查询为什么返回结果? 'and'子句检查是什么?

Am I right?
If so, then why does the query return result? what is the 'and' clause checking?

有人可以解释上述查询中使用AND与WHERE的区别吗?

Could someone explain the difference when using AND vs WHERE in the above query?

提前谢谢！

答

我说得对吗?

Am I right?

不.此查询:

select tableA.id, tableA.name, tableB.details 
from tableA
left join tableB
on tableB.id = tableA.id
and tableB.logId = '<logged_in_user>'

与以下内容有很大的区别:

Is very different from:

select tableA.id, tableA.name, tableB.details 
from tableA
left join tableB
on tableB.id = tableA.id
where tableB.logId = '<logged_in_user>'

这是加入的条件.

在第一种情况下，您从A领取，然后在具有匹配ID 和 logID的情况下与B联接，否则将详细信息保留为空.

In the first case, you take from A, then join with B when there's a matching id and a logId, else leave details null.

第二步，从A取走，然后在存在匹配ID的情况下与B联接，否则将详细信息保留为空，，然后仅保留A的行，其中B匹配具有logId -在此过程中从A中删除行，并实际上将其转换为内部联接.

In the second, you take from A, then join with B when there's a matching id, else leave details null, and then you keep only rows from A where there's a B match that has a logId -- eliminating rows from A in the process, and de facto turning it into an inner join.

SQL中AND与WHERE子句的用法

相关推荐