SQL alternative to left join

Question

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?

Why would you not use `LEFT JOIN`? Don't use the second query. — Eric, Feb 21 '18 at 16:56
Possible duplicate of [ANSI vs. non-ANSI SQL JOIN syntax](https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax) — Pred, Feb 21 '18 at 16:57
You can get the same result without `LEFT JOIN` by simply removing it: `Select DISTINCT e.id,e.name from employee e where e.id like 'D%'` (You can probably remove the DISTINC, too) And your 2nd query is quite useless, when you remove the DISTINCT you will see it's almost the same as a CROSS JOIN (only removing any possible NULL ids) — dnoeth, Feb 21 '18 at 17:01
If you are not selecting or filtering anything from the job table, why do you need it in your query? — isaace, Feb 21 '18 at 17:03
@Eric - I am actually trying to optimize a query, thus wanted to see the performance without a left join. — Allan Fernandes, Feb 21 '18 at 17:04
@dnoeth - That's what I was thinking, so it will still fetch me the same result right? without using `AND (e.id=j.id OR e.id <> j.id)`. I will try that and get back to you. Thanks. — Allan Fernandes, Feb 21 '18 at 17:06
@AllanFernandes You are performing a `LEFT JOIN`, you are just not stating it explicitly in the query. In most RDBMS, there will be no noticable performance difference, however, the older syntax is deprecated and you can find multiple articles describing pitfalls of it. — Pred, Feb 21 '18 at 17:07
@Pred: Where do you see a non-explicit Left Join? The 2nd Select joins *each* NOT NULL id from employee to *each* NOT NULL id from job, which is a Cross Join, but no Outer Join. — dnoeth, Feb 21 '18 at 19:39
@dnoeth In the where conditions:: `(e.id=j.id OR e.id <> j.id)` Try to look up the differences between ANSI and non-ANSI joins. — Pred, Feb 21 '18 at 20:46
@prad: I know the differences (btw, the old syntax is still Standard SQL) and #2 using explicit join syntax translates to: `from employee e join job j on (e.id=j.id OR e.id <> j.id)`. Every value in e.id will be matched to every valuein j.id, e.g. e.id: 1, j.id: 1,2,3,4,5 -> *1=1, 1<>2, 1<>3, 1<>4, 1<>5* -> all five rows match., It's the same as `e join j on e.id is not null and j.id is not null` — dnoeth, Feb 21 '18 at 21:27

Gordon Linoff · Accepted Answer · 2018-02-22T01:44:33.073

2

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.

edited Feb 22 '18 at 01:44

answered Feb 21 '18 at 17:06

Gordon Linoff

1,242,037
58
646
786

Performance might be better, but this will return a different result, i.e. an Inner Join. But the original Select is exactly the same as a `Select e.id,e.name from employee e where e.id like 'D%'` (assuming `id` is a unique column). No column from the inner table is accessed, thus there's no need to join at all. A decent optimizer (of course not the one in MySQL) will do this *join elimination* automatically. – dnoeth Feb 21 '18 at 19:35
@dnoeth . . . You are absolutely correct. I adjusted the answer, referring to your comment. – Gordon Linoff Feb 22 '18 at 01:45

SQL alternative to left join

1 Answers1