CODEWITHSUNDEEP
mysqlsqlleft-join
jitendragarg
jitendragarg

Reputation: 957

Creating a left join condition using where clause in sql

I want to create a condition in where clause, that can imitate left join. For example:

From Table1, Table2 Where table1.ID *= table2.ID

needs to be written as 

From Table1, Table2
Where Table1.ID = Table2.ID AND (some condition to get the rows from table1 where match does not exist)

I want to do this, because the existing query builder function calls like dozen functions to create the final query, and same function is called from 100+ pages. So, updating FROM clause to use Left Join will break the whole flow, and cause a whole lot of headache. I would rather just update the WHERE clause itself.

Upvotes: 0

Views: 1073

Answers (3)

Prasanth V J
Prasanth V J

Reputation: 1196

CREATE TABLE Table1
    (`Name` varchar(5), `Age` int)
;

INSERT INTO Table1
    (`Name`, `Age`)
VALUES
    ('PVJ', 10),
    ('EPF', 12),
    ('CISCO', 13)
;

CREATE TABLE Table2
    (`Name` varchar(3), `Age` int)
;

INSERT INTO Table2
    (`Name`, `Age`)
VALUES
    ('PVJ', 10),
    ('EPF', 12),
    ('DVF', 13)

select Table1.Name,(select Table2.Age from Table2 where Table1.Name=Table2.Name)  Age from table1

http://sqlfiddle.com/#!2/7dbd4/17

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271051

In general, a where clause cannot increase the number of rows in the result set. So, you cannot formally reproduce a left join in the where clause.

That said, it is possible that this would do what you want:

From Table1, Table2
Where (Table1.ID = Table2.ID or
       not exists (select 1 from table2 t2 where t2.id = table1.id) 
      )

This doesn't quite formally replicate the left join. For instance, if Table2 is empty, then the from clause returns no rows at all, so nothing is returned. However, it might work for your case.

Upvotes: 1

kamahl
kamahl

Reputation: 941

From my current understanding of relational-algebra, this is not possible. The left outer join only joins where a counterpart int the right table exists.

You could use two Queries, one being the join, and one being a select with "where not_exists(select 1 from table2 where table2.id=table1.id)" You then union those two sets, this will result in your outer join.

Upvotes: 0

Related Questions