Simulate a left join without using "left join"

Question

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

I have two tables, A and B, both with id and name columns. I would like to select all the dbids on both tables, where the name in A equals the name in 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.

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 join and wanted to know if/how it is possible to do the same thing.

Questions in the format "I want to do [thing] but I can't use [tool that is usually used to do [thing]]" are *usually* a sign that the problem is being approached in the wrong way. Please give some context around the real problem you are trying to solve, and the reason why you cannot use `LEFT JOIN` in your query. — Polynomial, Feb 06 '12 at 12:01
Why can't you use a `left join`, what do you want to archive with it? — Bjoern, Feb 06 '12 at 12:02
@Polynomial sometimes it's a sign of a homework assignment or interview question. Or maybe OP's `L`-key is broken. Maybe he will clarify... — Dennis Traub, Feb 06 '12 at 12:19
Sound like you want the relational operator [semi difference (a.k.a. antijoin)](http://stackoverflow.com/questions/7494586/get-non-existing-data-from-another-table-join/7496263#7496263) but, not knowing what it is called, name-checked the SQL concept commonly used to perform it. Not being able to use a left join is potentially interesting question but begs another: "What **can** you use?" — onedaywhen, Feb 06 '12 at 12:22
@DennisTraub - If it is a homework question, the answer "This is a bad idea, you should *always* use the right tools for the job." should be the only answer that gets full marks. — Polynomial, Feb 06 '12 at 12:24
@Polynomial: there are always about seven ways to do a given thing in SQL, two of which will be considered candidates and should be performance tested. SQL is not a one-size-fits-all kind of language. — onedaywhen, Feb 06 '12 at 12:27
@Polynomial true. Though most teachers/instructors probably wont have that kind of insight. — Dennis Traub, Feb 06 '12 at 12:28
@onedaywhen - I understand what you're saying, but I don't think this is a case of looking for the optimal method. It seems to be about already knowing the optimal method and choosing not to use it anyway. — Polynomial, Feb 06 '12 at 12:31
Preface: if java/jpa are strange words to you, stop reading now. In JPA left join only works if I tag the "relationship" field (@oneToMany, ...). As the table A is only a temporary table where to store the data to synchronize, I don't want/need a direct relationship between the two tables. So I'm trying to find another way. I hope to have brought more light on my question :) (and sorry for my english ;) ) — Francesco, Feb 06 '12 at 12:33
@Francesco - Not familiar with JPA but how are you going to use any of the answers without submitting an arbitrary SQL statement? And if you are going to submit an arbitrary SQL statement why not just use `LEFT JOIN`? If you have to use some intermediate layer that writes the SQL for you probably better to ask a new question tagged JPA rather than SQL to ask how to achieve your objective. — Martin Smith, Feb 06 '12 at 12:36
@Martin JPA manages relationships between tables. So in this case if you use left join, jpa "parses" the query and checks for a tag that defines the relationship. I'm not sure if I can use your answers, but before testing them, I have to know how can I avoid using left join. I'm not an SQL expert... — Francesco, Feb 06 '12 at 12:48
@onedaywhen: not necessary. The solution to my problem is only a sql matter (or at least I think) and I didn't mention Java and JPA deliberately. But sorry if I was wrong. — Francesco, Feb 06 '12 at 13:01

score 20 · Accepted Answer · edited Jul 07 '16 at 14:08

20

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

edited Jul 07 '16 at 14:08

chipairon

2,031
2
19
21

answered Feb 06 '12 at 12:04

Diego

34,802
21
91
134

Is it right that this solution works for all DBs (Oracle, MySql, PostGre, ...)? The one Polynomial suggested is the same as this, but it uses the NVL, which i.e MySql doesn't have... – Francesco Feb 06 '12 at 13:06
not sure, you would have to try. I only have access to SQL Server here – Diego Feb 06 '12 at 13:46
3

This is not equivalent to a `LEFT JOIN`. Or, to be accurate, it is equivalent only of `B(name)` has `UNIQUE` constraint. – ypercubeᵀᴹ Feb 19 '12 at 02:20
@Diego can you post a full code that simulates the left join? – pnkjmndhl Sep 19 '17 at 02:36
This works on postgres, but only to select one column – rdnobrega Oct 20 '20 at 16:51
I have used the same and It does work for ORACLE. Thank you! – DontDownvote May 26 '22 at 11:08

score 15 · Answer 2 · answered Feb 06 '12 at 12:05

15

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

answered Feb 06 '12 at 12:05

Martin Smith

438,706
87
741
845

20

If he can't use left joins, I doubt he can use right joins. – Jodevan Nov 24 '17 at 16:40

onedaywhen · Answer 3 · 2012-02-06T12:53:59.560

13

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

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 );

edited Feb 06 '12 at 12:53

answered Feb 06 '12 at 12:08

onedaywhen

55,269
12
100
138

Guffa · Answer 4 · 2012-02-06T12:08:24.670

6

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

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 join keyword was introduced. You should of course use the join keyword instead if possible. The old syntax might not even work in newer versions of the database.

edited Feb 06 '12 at 12:08

answered Feb 06 '12 at 12:02

Guffa

687,336
108
737
1,005

2

I still see `LEFT JOIN` in your query. Am I missing something? – Polynomial Feb 06 '12 at 12:04
2

`*=` is only for Microsoft SQL Server. Oracle uses `(+)`. All the others don't have such an operator at all. – Feb 06 '12 at 12:06
@Polynomial: You were just too quick to read it. I corrected that right after I posted the answer. :) – Guffa Feb 06 '12 at 12:06
@a_horse_with_no_name: That's right, I should have mentioned that. – Guffa Feb 06 '12 at 12:07

ErikE · Answer 5 · 2022-11-25T19:38:53.253

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

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 does not 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 LEFT in 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
```

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

score 4 · Answer 6 · edited Feb 06 '12 at 12:02

4

You could use subqueries, something like:

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

edited Feb 06 '12 at 12:02

Polynomial

27,674
12
80
107

answered Feb 06 '12 at 12:01

rfcdejong

2,219
1
25
51

score 2 · Answer 7 · edited May 23 '17 at 11:54

2

you can use oracle + operator for left join :-

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

Find link :-

Oracle "(+)" Operator

edited May 23 '17 at 11:54

Community

1
1

answered May 27 '15 at 11:16

Vijay

4,694
1
30
38

score 1 · Answer 8 · edited Feb 20 '12 at 16:39

1

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

edited Feb 20 '12 at 16:39

Matt Fenwick

48,199
22
128
192

answered Feb 06 '12 at 12:14

JStead

1,710
11
12

score 0 · Answer 9 · answered Jun 09 '18 at 11:27

Simulating left join using pure simple 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

score 0 · Answer 10 · answered Feb 06 '12 at 12:14

0

I'm not sure if you just can't use a LEFT JOIN or if you're restricted from using any JOINS at all. But as far as I understand your requirements, an INNER JOIN should work:

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

answered Feb 06 '12 at 12:14

Dennis Traub

50,557
7
93
108

Simulate a left join without using "left join"

10 Answers10

Linked