简单但嵌套的SELECT查询 [英] Simple but nested SELECT query

问题描述

我有四个表a,b,c,d.在表a中，我有一对id, name.在表b中，我有一对idx, idy. b.idy来自表a.id.在表c, d中，我有一对与表b.idx相关的id, value.

I have four tables a,b,c,d. In table a I have pair of id, name. In table b I have pair of idx, idy. The b.idy comes from table a.id. In tables c, d I have pair of id, value which are related to table b.idx.

我必须执行这样的查询:

I have to perform a query like this:

SELECT c.value, d.value 
FROM a,b,c,d
WHERE a.name = "test" AND b.idy = a.id AND (c.id = b.idx AND d.id = b.idx)

问题是有时表c, d中缺少记录，因此AND将返回零记录，但是如果c或d中有可用记录，我需要得到结果.另外，我不能使用OR，因为它还会为两个表返回其他行.

The problem is sometimes there are missing records in tables c, d so AND will return zero records, but I need to have results if there are available either in c or d. Also I can't use OR because it returns other rows also for both tables.

我想会有一个使用UNION甚至只是嵌套的SELECT的解决方案.我更喜欢不使用JOIN或使用单独的查询.

I guess there would be a solution using UNION or even just nested SELECTs. I prefer to do not use JOIN or using separated quires.

提前谢谢！

更新:

避免使用JOIN的原因是性能.我现在正在处理的结构要比此结构复杂得多，所以我确信JOIN在不久的将来我会遇到严重的性能问题.

The reason for avoiding using JOIN is performance. The structure that I'm working on now is much more complex rather than this one, so I'm sure with JOIN I would have serious performance issues in the upcoming future.

推荐答案

我认为您想使用LEFT JOIN:

SELECT c.value, d.value 
FROM a
LEFT JOIN b
    ON a.id = b.idy
LEFT JOIN c
    ON b.idx = c.id
LEFT JOIN d
    ON b.idx = d.id
WHERE a.name = "test" 

使用逗号联接语法是INNER JOIN，它要求所有表中的记录均可用.

Using the comma join syntax is an INNER JOIN which requires that the records be available in all tables.

如果您在查看JOIN语法时需要帮助，则有一个有用的指南:

If you need help in reviewing JOIN syntax there is a helpful guide:

SQL连接的直观说明

这篇关于简单但嵌套的SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！