SQL查询返回至少包含参数的行(即使未找到) [英] SQL query to return a row even if not found, with at least in parameters

问题描述

我想编写一个SQL查询(oracle) 知道是否有操作(由ope.ope_operationid标识) 至少具有某种类型的操作(opt.opt_id)，如果没有，则表明它不在结果中.

I would like to write a SQL query (oracle) to know if an operation (identified by ope.ope_operationid) has at least an operation of a certain type (opt.opt_id), and if it does not, to show that it doesn't in the results.

例如，我有此操作LAA351BP(我知道此操作存在于库中)， 我想知道它是否至少具有ID为3781的操作类型. 如果有，则打印所有内容，如果没有，则打印operationid 以及旁边的未找到"之类的东西

For example, I have this operation LAA351BP (I know this one exists in base), and I would like to know if it has at least an operation type which id is 3781. If it has, print everything, if it hasn't, print the operationid and something like 'not found' next to it

nvl是要使用的功能吗?看来我无法使其正常工作.

Is nvl the function to use ? It seems I can't get it to work properly.

SELECT  DISTINCT ope.ope_operationid,
ser.ser_code,
opt.opt_code,
ost.ost_code
FROM    od_operation ope, 
od_service_type ser,
od_operation_type opt,
od_status_type ost,
od_equipment_type eqt,
WHERE   ope.ser_id = ser.ser_id
AND     opt.opt_id = ope.opt_id
AND     ost.ost_id = ope.ost_id
AND     ope.opt_id = 3781
AND     ope.ope_operationid = 'LAA351BP'

谢谢

推荐答案

您应该开始使用标准的JOIN语法.除了更具可读性(至少在我看来)，如果您忘记了WHERE子句中的实际联接条件，它还可以防止意外的笛卡尔联接.另外，它可以在几乎所有DBMS上移植，这与Oracle使用的笨拙的(+)语法相反(它也具有JOIN语法所没有的一些限制)

You should start using standard JOIN syntax. Apart from being more readable (at least in my opinion) it also protects you from accidental cartesian joins if you forget the actual join condition in the WHERE clause. Plus it is portable across nearly all DBMS as opposed to the clunky (+) syntax used by Oracle (which also has some limitations that the JOIN syntax does not have)

以下是使用显式(而不是隐式)联接重写的查询:

Here is the query re-written using explicit (instead of implicit) joins:

SELECT DISTINCT ope.ope_operationid,
       ser.ser_code,
       opt.opt_code,
       ost.ost_code
FROM od_operation ope, 
   LEFT JOIN od_service_type ser ON ope.ser_id = ser.ser_id
   LEFT JOIN od_operation_type opt ON opt.opt_id = ope.opt_id
   LEFT JOIN od_status_type ost ON ost.ost_id = ope.ost_id
   LEFT JOIN od_equipment_type eqt ON ????????
WHERE ope.opt_id = 3781
AND   ope.ope_operationid = 'LAA351BP'

编辑

od_equipment_type上缺少的联接条件正是使用JOIN语法的原因.如果删除了原始SQL中的尾部逗号，则该语句将创建不需要的笛卡尔联接，如果涉及的表很大，则可能会对服务器造成严重影响.

The missing join condition on od_equipment_type is exactly the reason why the JOIN syntax is preferred. If the trailing comma in the original SQL is removed, the statement would create an unwanted cartesian join which might have a severe impact on the server if the involved tables are big.

使用JOIN语法，您将始终会遇到语法错误，从而避免出现此类拼写错误.仅当您在FROM列表中保留逗号时，使用隐式联接才会给您一个错误，但是如果您错过了WHERE

With the JOIN syntax you will always get a syntax error which prevents you from such typos. Using implicit joins will only give you an error when you leave a comma in the FROM list, but never if you miss a join condition in the WHERE

这篇关于SQL查询返回至少包含参数的行(即使未找到)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！