CODEWITHSUNDEEP
mysqlsqljoinleft-join
Allan Fernandes
Allan Fernandes

Reputation: 27

SQL alternative to left join

I have this query that performs left join on tables and returns some rows.

Query with left join:

Select DISTINCT 
e.id,e.name
from employee e
left join
job j on j.id = e.id
where e.id like 'D%'

Now, for the same query, I wanted to get the result without using left join and I was able to produce this SQL query that gave me the same result as the above query.

Query without left join:

Select DISTINCT 
e.id,e.name
from employee e, job j
where e.id like 'D%' AND (e.id=j.id  OR  e.id <> j.id)

Although the query returns the same result as above, my question is : Whether the second query makes sense and Whether it is equivalent to left join?

Upvotes: 1

Views: 6163

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 2

Related Questions