使oracle左连接查询变得简单 [英] Making oracle left join query simple

问题描述

大家好，



我有2张桌子：



1. ppl

Hi all,

I have 2 tables:

1. ppl

ename | edept
ram |	sales
sham |	sales
jadu |	hr
madhu |	hr
sita |	sales
mohan |	sales

2.任务

2. task

ename | tdate
ram |	01-10-17
jadu |	01-10-17
mohan |	01-10-17

对于tdate = '01 -oct-2017'，输出应为：

For tdate='01-oct-2017', output should be:

ename |	edept |	tdate
jadu |	hr |	01-10-2017
madhu |	hr |	
mohan |	sales |	01-10-2017
ram |	sales |	01-10-2017
sham |	sales |	
sita |	sales |	

基本上是ppl的左连接，具有特定tdate的任务



这是我的查询。它可以工作但可以简单吗？

Basically a left join of ppl with task for particular tdate

Here is my query. It works but can it be any simple?

select ppl.ename,ppl.edept,ntbl.tdate from
ppl left join
(select task.ename as ename , ppl.edept, task.tdate as tdate
from ppl,task
where ppl.ename= task.ename 
and task.tdate='01-oct-2017') ntbl
on ppl.ename=ntbl.ename
order by ename;

我尝试过：

What I have tried:

select ppl.ename as ename , ppl.edept, task.tdate as tdate
from ppl left join task
on ppl.ename=task.ename
where task.tdate='01-oct-2017';

这不能按预期工作，只能用于任务中的ename。

This does not work as expected and only gives for ename in task.

推荐答案

这是因为where条件，它将输出限制为通过where条件的行。删除它，您的查询将工作。但是，您需要重新考虑Db设计或您的查询。



要查看左连接是否有效，请写一些类似

That is because of the where condition, it restricts the output to the rows which pass the where condition. Remove it, and your query will work. However, you need to reconsider you Db design or your query.

To see if the left join works, write something like

select ppl.ename as ename , ppl.edept, task.tdate as tdate
from ppl left join task
on ppl.ename=task.ename
where ppl.edept='sales';

Quote：

select ppl.ename as ename , ppl.edept, task.tdate as tdate
from ppl left join task
on ppl.ename=task.ename
where task.tdate='01-oct-2017';

您的 LEFT JOIN 返回<的所有行code> ppl ，以及来自任务的所有匹配行。对于 ppl 没有相应任务行的行，结果将为 Null 所有任务列的值。



您的 WHERE 子句然后根据任务中等于的 tdate 列过滤结果一个特定的价值。对于 ppl 没有任务行的行，此过滤器将不，因为 Null 不等于该值。



您需要将日期过滤器移至 JOIN ：

Your LEFT JOIN returns all the rows from ppl, and any matching rows from task. For ppl rows which don't have a corresponding task row, the result will have Null values for all of the task columns.

Your WHERE clause then filters the result based on the tdate column from task being equal to a specific value. For ppl rows with no task rows, this filter will not be met, because Null is not equal to that value.

You need to move the date filter up to the JOIN:

SELECT
    ppl.ename, 
    ppl.edept, 
    task.tdate
FROM
    ppl 
    LEFT JOIN task
    ON ppl.ename = task.ename
    AND task.tdate = '01-oct-2017'
;

这篇关于使oracle左连接查询变得简单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！