Convert INNER JOIN to LEFT JOIN - Sql

Question

i have a query which returns ~ 97k records because i am using an inner join. Now i want to use left join to show also the null records on the right table(should be about 300k records). This is the actual query(INNER JOIN):

 select artnr as "Article number", ardbez1 as "Article description ", arpadrnr as "Supplier code", cd.ADPPHYSLAND as "Country of this supplier code", p.adrnr as "Parent supplier code", p.adrkname as "Name of parent", pd.ADPPHYSLAND as "Country of parent ", arppreis as "Price", arpwae as "Unit", arzwert as "COO", atatarnr as "HS Code"
    from mic_ccs_artikel, mic_ccs_artikeldetail, mic_ccs_artikelpreis, mic_dna_adressen c, mic_dna_adrdetail cd, mic_dna_adressen p, mic_dna_adrrelation, mic_dna_adrdetail pd, mic_ccs_artikelzoll, mic_ccs_artikeltarif
    where artsid = ardartsid
    and arddatstart <= sysdate
    and nvl(arddatend, '01.01.4000') >= sysdate
    and artsid = arpartsid
    and ARPPREISART = 'VP'
    and arpdatstart <= sysdate
    and nvl(arpdatend, '01.01.4000') >= sysdate
    and c.adrsid = cd.adpadrsid
    and arpadrnr = c.adrnr
    and cd.adpdatvon <= sysdate
    and nvl(cd.adpdatbis, '01.01.4000') >= sysdate
    and c.adrmandant = 'S1'
    and c.adrwerk = 'V1'
    and p.adrmandant = 'S1'
    and p.adrwerk = 'V1'
    and p.adrsid = arlrelsid1
    and c.adrsid = arlrelsid2
    and p.adrsid = pd.adpadrsid
    and pd.adpdatvon <= sysdate
    and nvl(pd.adpdatbis, '01.01.4000') >= sysdate
    and artsid = arzartsid
    and arztyp = 'URLD'
    and arzadrnr = c.adrnr
    and arzdatstart <= sysdate
    and nvl(arzdatend, '01.01.4000') >= sysdate
    and artsid = ataartsid
    and ATAREGION = 'SE' 
    and atatarart='EXPORT'
    and atadatstart <= sysdate
    and nvl(atadatend, '01.01.4000') >= sysdate
    ;

Then i tried to "convert" it to LEFT JOIN, but it's always showing the same result:

select artnr as "Article number", ardbez1 as "Article description ", arpadrnr as "Supplier code", cd.ADPPHYSLAND as "Country of this supplier code", p.adrnr as "Parent supplier code", p.adrkname as "Name of parent", pd.ADPPHYSLAND as "Country of parent ", arppreis as "Price", arpwae as "Unit", arzwert as "COO", atatarnr as "HS Code"
from mic_ccs_artikel, mic_ccs_artikeldetail, mic_ccs_artikelpreis, mic_dna_adressen c, mic_dna_adrdetail cd, mic_dna_adressen p, mic_dna_adrrelation, mic_dna_adrdetail pd, mic_ccs_artikelzoll, mic_ccs_artikeltarif
where artsid = ardartsid(+)
and arddatstart <= sysdate
and nvl(arddatend, '01.01.4000') >= sysdate
and artsid = arpartsid(+)
and ARPPREISART = 'VP'
and arpdatstart <= sysdate
and nvl(arpdatend, '01.01.4000') >= sysdate
and c.adrsid = cd.adpadrsid(+)
and arpadrnr = c.adrnr
and cd.adpdatvon <= sysdate
and nvl(cd.adpdatbis, '01.01.4000') >= sysdate
and c.adrmandant = 'S1'
and c.adrwerk = 'V1'
and p.adrmandant = 'S1'
and p.adrwerk = 'V1'
and p.adrsid = arlrelsid1(+)
and c.adrsid = arlrelsid2
and p.adrsid = pd.adpadrsid
and pd.adpdatvon <= sysdate
and nvl(pd.adpdatbis, '01.01.4000') >= sysdate
and artsid = arzartsid(+)
and arztyp = 'URLD'
and arzadrnr = c.adrnr
and arzdatstart <= sysdate
and nvl(arzdatend, '01.01.4000') >= sysdate
and artsid = ataartsid(+)
and ATAREGION = 'SE' 
and atatarart='EXPORT'
and atadatstart <= sysdate
and nvl(atadatend, '01.01.4000') >= sysdate
;

Why is it always showing the same result?

use alias , we cant understand which columns are belong to which table — CompEng, May 31 '17 at 09:13
You should qualify every attribute, use aliases and start using ANSI join syntax (join...on...). Your immediate problem is probably that your left join'ed table also has a condition in your `where`, effectively turning it into an inner join. — HoneyBadger, May 31 '17 at 09:14
Use the ANSI join syntax - you'll find it much easier to spot your mistake, and so will the readers of your question! — Lord Peter, May 31 '17 at 09:15
as HoneyBadger mentioned. Get your query with all alias.column references. Once that is done, we can better help clean up your query syntax. — DRapp, May 31 '17 at 10:55
Possible duplicate of [Using WHERE in SQL](https://stackoverflow.com/questions/42011039/using-where-in-sql) — KindaTechy, May 31 '17 at 18:00

score 1 · Answer 1 · answered May 31 '17 at 18:09

Echoing the sentiments in the comments, using a left join and a where clause can result in some unexpected behavior. See my answer here for more details as to why that happens. Basically, you should switch to using ANSI syntax and alias your tables. In your case, that would look something like

select * -- select whichever fields you want here
    from mic_ccs_artikel a -- only one table in the from clause
    left join mic_ccs_artikeldetail b on b.someID = a.someID -- join conditions here
    left join mic_ccs_artikelpreis c
        on c.someID = a.someID -- join conidition
        and c.someField = 'Some Property' -- filtering here
    ...

The secondary join conditions removes the risk of throwing out those records in the where clause. Again, see my other answer for more details on that.

Convert INNER JOIN to LEFT JOIN - Sql

1 Answers1