CODEWITHSUNDEEP
mysqlsqlpostgresql-9.4
Surya
Surya

Reputation: 2699

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

Trying to get the same result using a where clause instead of left join

TableA schema:

id
name
created_by

TableB schema:

id 
name
updated_by

Sample TableA

id  name    created_by
1   pen      a
2   paste    k

Sample TableB

id  name        updated_by
1   inkpen      b
1   ballpen     c

Query with left join

select tablea.id, tableb.id, tablea.name, tableb.name
from tablea
    left join tableb on tableb.id = tablea.id and tableb.updated_by = 'a'

Result

tablea.id       tableb.id       tablea.name     tableb.name
    1            NULL               pen           NULL

Query using where clause:

select tablea.id, tableb.id, tablea.name, tableb.name
from tablea left join tableb
ON tableb.id = tablea.id WHERE tableb.updated_by = 'a'

Result

tablea.id       tableb.id       tablea.name     tableb.name
    NULL             NULL           NULL              NULL

We were using a function before where we passed a user_id. The function, in turn, returned a table and used the user_id in the left join. Since the function was not using the indexes, we decided to use views instead. However, in views, we cannot pass variables. Hence we were not able to use tableb.updated_by in Left join, so tried the same query in where clause.

How can we write the query such that I can get the same result as in left join through where clause?

Upvotes: 1

Views: 3142

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

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;

Upvotes: 1

O. Jones
O. Jones

Reputation: 108706

How can I write the query such that I can get the same result as in left join through where clause?

You can't.

The conditions in the ON clause of a LEFT JOIN, when unmet, join the first table's row with null values replacing a row from the second table. If those conditions appear in the WHERE clause, they exclude the first row when unmet. This effectively converts your LEFT JOIN into an ordinary inner JOIN.

Upvotes: 1

Related Questions