Why does left join in redshift not working?

Viewed 20

We are facing a weird issue with Redshift and I am looking for help to debug it please. Details of the issue are following:

I have 2 tables and I am trying to perform left join as follows:

select count(*) 
from abc.orders ot 
left outer join abc.events e on **ot.context_id = e.context_id**
where ot.order_id = '222:102'

Above query returns ~7000 records. Looks like it is performing default join as we have only 1 record in [Orders] table with Order ID = ‘222:102’

select count(*) 
from abc.orders ot 
left outer join abc.events e on **ot.event_id = e.event_id** 
where ot.order_id = '222:102'

Above query returns 1 record correctly. If you notice, I have just changed column for joining 2 tables. Event_ID in [Events] table is identity column but I thought I should get similar records even if I use any other column like Context_ID.

Further, I tried following query under the impression it should return all the ~7000 records as I am using default join but surprisingly it returned only 1 record.

select count(*) 
from abc.orders ot 
**join** abc.events e on ot.event_id = e.event_id 
where ot.order_id = '222:102'

Following are the Redshift database details:

Cutdown version of table metadata:

CREATE TABLE abc.orders (
    order_id character varying(30) NOT NULL ENCODE raw,
    context_id integer ENCODE raw,
    event_id character varying(21) NOT NULL ENCODE zstd,
    FOREIGN KEY (event_id) REFERENCES events_20191014(event_id)
)
DISTSTYLE EVEN
SORTKEY ( context_id, order_id );
CREATE TABLE abc.events (
    event_id character varying(21) NOT NULL ENCODE raw,
    context_id integer ENCODE raw,
    PRIMARY KEY (event_id)
)
DISTSTYLE ALL
SORTKEY ( context_id, event_id );

Database: Amazon Redshift cluster

I think, I am missing something essential while joining the tables. Could you please guide me in right direction?

Thank you

0 Answers
Related