How to Substitute a Left Join in Sql

SQL alternative to left join

I think exists is the more natural method:

Select e.id, e.name
from employee e
where exists (select 1 from job j where j.id = e.id) and
      e.id like 'D%';

This should also have much better performance.

EDIT:

Dnoeth makes a really good point. I missed the "left" in the left join. The simplest query is:

Select e.id, e.name
from employee e
where e.id like 'D%';

I would expect the id to be unique so select distinct is not needed.

How to replace a condition in left join through a where clause?

Your real tables probably look like this (primary keys bold):

• item (item_id, name, created_by_user_id)
• itemuserupdate (item_id, updated_by_user_id, name)
• users (user_id, name)

What you can do is get all user/item combinations first and then outer join the existing entries:

create myview as
select i.item_id, i.name, u.user_id, iu.name as name_by_user
  i.item_id,
  u.user_id,
from users u
cross join item i
left outer join itemuserupdate iu on iu.itemid = i.itemid
                                 and iu.updated_by_user_id = u.user_id;

You then then use this view with

select item_id, name, name_by_user from myview where user_id = 123;

Replacing oracle specific notation (+) with LEFT JOIN and where clause

The equivalent syntax would be:

SELECT x.a, y.a, z.a
FROM x LEFT JOIN
     y
     ON x.k = y.k LEFT JOIN
     z
     ON x.p = z.p;

how to use replace on a left join statement

Most likely you have got an extraneous ON keyword in your JOIN clause:

SELECT
    idticket, bt.matricula, bv.vehicle
FROM
    b_ticket AS bt LEFT JOIN b_vehicle AS bv
        ON REPLACE(bv.matricula, ' ', '') ILIKE REPLACE(bt.matricula, ' ', '')
WHERE
    date_start >= '2019/01/01/' AND date_end <= '2020/01/01';

As the error states: predicate must be any valid boolean expression, but it cannot have a ON keyword in it.

---

Related Topics