Francesco Francesco - 4 months ago 48
SQL Question

Simulate a left join without using "left join"

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.

Answer

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 
Comments