SQL 不使用“左连接”模拟左连接

Question

提问by Francesco

I need to simulate the left join effect without using the "left join" key.

我需要在不使用“左连接”键的情况下模拟左连接效果。

I have two tables, A and B, both with idand namecolumns. I would like to select all the dbids on both tables, where the name in A equals the name in B.

我有两个表，A 和 B，都有id和name列。我想选择两个表上的所有 dbids，其中 A 中的名称等于 B 中的名称。

I use this to make a synchronization, so at the beginning B is empty (so I will have couples with id from A with a value and id from B is null). Later I will have a mix of couples with value - value and value - null.

我用它来进行同步，所以一开始 B 是空的（所以我会有一对来自 A 的 id 带有一个值，而来自 B 的 id 为空）。稍后我将混合使用 value - value 和 value - null。

Normally it would be:

通常它会是：

SELECT A.id, B.id
FROM A left join B
ON A.name = B.name

The problem is that I can't use the left joinand wanted to know if/how it is possible to do the same thing.

问题是我不能使用left join并且想知道是否/如何做同样的事情。

Answer 1

采纳答案by Diego

you can use this approach, but you must be sure that the inner select only returns one row.

您可以使用这种方法，但您必须确保内部选择只返回一行。

SELECT A.id, 
(select B.id from B where  A.name = B.name) as B_ID
FROM A

Answer 2

回答by Martin Smith

Just reverse the tables and use a right join instead.

只需反转表格并使用正确的连接即可。

SELECT A.id,
       B.id
FROM   B
       RIGHT JOIN A
         ON A.name = B.name

Answer 3

回答by onedaywhen

I'm not familiar with java/jpa. Using pure SQL, here's one approach:

我不熟悉 java/jpa。使用纯 SQL，这是一种方法：

SELECT A.id AS A_id, B.id AS B_id
  FROM A INNER JOIN B
          ON A.name = B.name
UNION
SELECT id AS A_id, NULL AS B_id 
  FROM A 
 WHERE name NOT IN ( SELECT name FROM B );

Answer 4

回答by Guffa

In SQL Server, for example, You can use the *=operator to make a left join:

例如，在 SQL Server 中，您可以使用*=运算符进行左连接：

select A.id, B.id
from A, B
where A.name *= B.name

Other databases might have a slightly different syntax, if such an operator exists at all.

如果存在这样的运算符，其他数据库的语法可能略有不同。

This is the old syntax, used before the joinkeyword was introduced. You should of course use the joinkeyword instead if possible. The old syntax might not even work in newer versions of the database.

这是在join引入关键字之前使用的旧语法。join如果可能，您当然应该改用关键字。旧语法甚至可能不适用于较新版本的数据库。

Answer 5

回答by rfcdejong

You could use subqueries, something like:

您可以使用子查询，例如：

select a.id
,      nvl((select b.id from b where b.name = a.name), "") as bId
from a

Answer 6

回答by ErikE

I can only think of two ways that haven't been given so far. My last three ideas have already been given (boohoo) but I put them here for posterity. I DID think of them without cheating. :-p

目前只能想到两种方法没有给出。我的最后三个想法已经给出（嘘），但我把它们放在这里供后代使用。我没有作弊就想到了他们。:-p

Calculate whether B has a match, then provide an extra UNIONed row for the B set to supply the NULL when there is no match.

SELECT A.Id, A.Something, B.Id, B.Whatever, B.SomethingElse
FROM
   (
      SELECT
         A.*,
         CASE
            WHEN EXISTS (SELECT * FROM B WHERE A.Id = B.Id) THEN 1
            ELSE 0
         END Which
      FROM A
   ) A
   INNER JOIN (
      SELECT 1 Which, B.* FROM B
      UNION ALL SELECT 0, B* FROM B WHERE 1 = 0
   ) B ON A.Which = B.Which
      AND (
         A.Which = 0
         OR (
            A.Which = 1
            AND A.Id = b.Id
         )
      )

A slightly different take on that same query:

SELECT A.Id, B.Id
FROM
   (
      SELECT
         A.*,
         CASE
            WHEN EXISTS (SELECT * FROM B WHERE A.Id = B.Id) THEN A.Id
            ELSE -1 // a value that doesn't exist in B
         END PseudoId
      FROM A
   ) A
   INNER JOIN (
      SELECT B.Id PseudoId, B.Id FROM B
      UNION ALL SELECT -1, NULL
   ) B ON A.Which = B.Which
      AND A.PseudoId = B.PseudoId

Only for SQL Server specifically. I know, it's really a left join, but it doesn't SAY LEFTin there!

SELECT A.Id, B.Id
FROM
   A
   OUTER APPLY (
      SELECT *
      FROM B
      WHERE A.Id = B.Id
   ) B

Get the inner join then UNION the outer join:

SELECT A.Id, B.Id
FROM
   A
   INNER JOIN B ON A.name = B.name
UNION ALL 
SELECT A.Id, NULL
FROM A
WHERE NOT EXISTS (
   SELECT *
   FROM B
   WHERE A.Id = B.Id
)

Use RIGHT JOIN. That's not a LEFT JOIN!

SELECT A.Id, B.Id
FROM
   B
   RIGHT JOIN A ON B.name = A.name

Just select the B value in a subquery expression (let's hope there's only one B per A). Multiple columns from B can be their own expressions (YUCKO!):
```
SELECT A.Id, (SELECT TOP 1 B.Id FROM B WHERE A.Id = B.Id) Bid
FROM A
```

计算 B 是否有匹配项，然后为 B 集提供一个额外的 UNIONed 行，以便在没有匹配项时提供 NULL。

SELECT A.Id, A.Something, B.Id, B.Whatever, B.SomethingElse
FROM
   (
      SELECT
         A.*,
         CASE
            WHEN EXISTS (SELECT * FROM B WHERE A.Id = B.Id) THEN 1
            ELSE 0
         END Which
      FROM A
   ) A
   INNER JOIN (
      SELECT 1 Which, B.* FROM B
      UNION ALL SELECT 0, B* FROM B WHERE 1 = 0
   ) B ON A.Which = B.Which
      AND (
         A.Which = 0
         OR (
            A.Which = 1
            AND A.Id = b.Id
         )
      )

对同一查询的看法略有不同：

SELECT A.Id, B.Id
FROM
   (
      SELECT
         A.*,
         CASE
            WHEN EXISTS (SELECT * FROM B WHERE A.Id = B.Id) THEN A.Id
            ELSE -1 // a value that doesn't exist in B
         END PseudoId
      FROM A
   ) A
   INNER JOIN (
      SELECT B.Id PseudoId, B.Id FROM B
      UNION ALL SELECT -1, NULL
   ) B ON A.Which = B.Which
      AND A.PseudoId = B.PseudoId

仅适用于 SQL Server。我知道，这确实是一个左连接，但它并没有LEFT在那里说！

SELECT A.Id, B.Id
FROM
   A
   OUTER APPLY (
      SELECT *
      FROM B
      WHERE A.Id = B.Id
   ) B

获取内连接然后 UNION 外连接：

SELECT A.Id, B.Id
FROM
   A
   INNER JOIN B ON A.name = B.name
UNION ALL 
SELECT A.Id, NULL
FROM A
WHERE NOT EXISTS (
   SELECT *
   FROM B
   WHERE A.Id = B.Id
)

使用右连接。那不是左连接！

SELECT A.Id, B.Id
FROM
   B
   RIGHT JOIN A ON B.name = A.name

只需在子查询表达式中选择 B 值（希望每个 A 只有一个 B）。B 中的多个列可以是它们自己的表达式（YUCKO！）：
```
SELECT A.Id, (SELECT TOP 1 B.Id FROM B WHERE A.Id = B.Id) Bid
FROM A
```

Anyone using Oracle may need some FROM DUALclauses in any SELECTs that have no FROM.

任何使用 Oracle 的人都可能需要FROM DUAL在没有 FROM 的任何 SELECT 中使用一些子句。

Answer 7

回答by Opster Elasticsearch Pro-Vijay

you can use oracle + operator for left join :-

您可以使用 oracle + 运算符进行左连接：-

SELECT A.id, B.id FROM A , B ON A.name = B.name (+)

Find link :-

查找链接：-

Oracle "(+)" Operator

甲骨文“(+)”运算符

Answer 8

回答by JStead

SELECT A.id, B.id
FROM A full outer join B
ON A.name = B.name
where A.name is not null

Answer 9

回答by Dennis Traub

I'm not sure if you just can't use a LEFT JOINor if you're restricted from using any JOINSat all. But as far as I understand your requirements, an INNER JOINshould work:

我不确定您是不能使用 aLEFT JOIN还是根本无法使用任何JOINS。但据我了解您的要求，INNER JOIN应该可以：

SELECT A.id, B.id
FROM A 
INNER JOIN B ON A.name = B.name

Answer 10

回答by Catalin Timofticiuc

Simulating left join using pure simple sql:

使用纯简单的 sql 模拟左连接：

SELECT A.name
FROM A
where (select count(B.name) from B where  A.id = B.id)<1;

In left join there are no lines in B referring A so 0 names in B will refer to the lines in A that dont have a match + or A.id = B.id in where clause to simulate the inner join

在左连接中，B 中没有引用 A 的行，因此 B 中的 0 个名称将引用 A 中没有匹配的行 + 或 A.id = B.id 在 where 子句中模拟内连接

SQL 不使用“左连接”模拟左连接

提问by Francesco

采纳答案by Diego

回答by Martin Smith

回答by onedaywhen

回答by Guffa

回答by rfcdejong

回答by ErikE

回答by Opster Elasticsearch Pro-Vijay

回答by JStead

回答by Dennis Traub

回答by Catalin Timofticiuc

相关推荐

最近更新

标签

SQL 不使用“左连接”模拟左连接

提问by Francesco

采纳答案by Diego

回答by Martin Smith

回答by onedaywhen

回答by Guffa

回答by rfcdejong

回答by ErikE

回答by Opster Elasticsearch Pro-Vijay

回答by JStead

回答by Dennis Traub

回答by Catalin Timofticiuc

相关推荐

SQL 不为空时的Sql Case语句

SQL Postgresql 包含在 where 子句中

T-SQL：如何在值列表中选择不在表中的值？

SQL 从每组中选择前 1 行

相关推荐

最近更新

标签