如何在不使用JOIN的情况下实现SQL连接? [英] How to implement SQL joins without using JOIN?

问题描述

不使用JOIN关键字如何实现SQL连接?

How does one implement SQL joins without using the JOIN keyword?

这并不是真的必要，但是我认为通过这样做，我可以更好地理解联接的实际作用.

This is not really necessary, but I thought that by doing this I could better understand what joins actually do.

推荐答案

基本的INNER JOIN易于实现. 以下:

The basic INNER JOIN is easy to implement. The following:

SELECT L.XCol, R.YCol
FROM LeftTable AS L
INNER JOIN RightTable AS R
ON L.IDCol=R.IDCol;

等效于:

SELECT L.XCol, R.YCol
FROM LeftTable AS L, RightTable AS R
WHERE L.IDCol=R.IDCol;

要将其扩展到LEFT/RIGHT/FULL OUTER JOIN，您只需要将不匹配的行以及正确列中的NULL联合到先前的INNER JOIN.

In order to extend this to a LEFT/RIGHT/FULL OUTER JOIN, you only need to UNION the rows with no match, along with NULL in the correct columns, to the previous INNER JOIN.

对于左外部联接，添加:

For a LEFT OUTER JOIN, add:

UNION ALL
SELECT L.XCol, NULL /* cast the NULL as needed */
FROM LeftTable AS L
WHERE NOT EXISTS (
    SELECT * FROM RightTable AS R
    WHERE L.IDCol=R.IDCol)

要进行正确的外部联接，请添加:

For a RIGHT OUTER JOIN, add:

UNION ALL
SELECT NULL, R.YCol /* cast the NULL as needed */
FROM RightTable AS R
WHERE NOT EXISTS (
    SELECT * FROM LeftTable AS L
    WHERE L.IDCol=R.IDCol)

要进行外部联接，请同时添加以上两项.

For a FULL OUTER JOIN, add both of the above.

这篇关于如何在不使用JOIN的情况下实现SQL连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！