[SOLVED] Full outer join

Hello,

I am writing SQL queries in order to generate question. My metabase is linked to a postgresql database and I am wondering why the full outer join is not working but the left join is. I mean that if I just replace the “full outer join” in my query buy a “left join”, it works.

I wonder how is it possible? Am I missing something?

Thank you

Hi @Yacine
Which version of Metabase?
What does “not working” means? Does it create an error?
There should be no replacement of any syntax besides filters of the SQL. You should post the query.

Hello @flamber, thank you for your response. Here is the code. The “left join” is at the end. It gives me some results. But when I replace it by “full outer join” I get nothing. There are no results (as if there was nothing to join on).
From what I understand, if a left join is giving some results the full outer join should give me results.

with NewRequestReceived as
    (   
        Select  *
        from events
        where (
        event_name = 'New Request Received'
        and agency_id = {{agency_id}})
    ),
    MeetingScheduled as
    (
        Select  *
        from events
        WHERE (
        "public"."events"."event_name" = 'Time Picker - Meeting Booked'
        and agency_id = {{agency_id}})
    )
Select  NewRequestReceived.source as "Portail",
        count(NewRequestReceived.unique_identifier) as "# demandes",
        count(MeetingScheduled.unique_identifier) as "# Rdv",
        100*count(MeetingScheduled.unique_identifier)/count(NewRequestReceived.unique_identifier) as "% conversion"
    from 
        NewRequestReceived
        left join
        MeetingScheduled
        on NewRequestReceived.unique_identifier = MeetingScheduled.unique_identifier
group by "Portail";

@Yacine
Which version of Metabase?
Which database are you querying?
What filter type is {{agency_id}}?

@flamber Thank you for your time

I have found where the issue comes from. I have a division by 0 but as Metabase does not show details on why there are no results, so I did not spot it.

Do you have any idea on how to enable a higher level of verbose on the SQL interpreter ?(if this last question is not clear, just tell me :slight_smile: )

@Yacine
Which version of Metabase?
Which database are you querying?

I am querying a Postgres Database. The version I am using is Metabase 0.33.0-preview but I was using an previous version when I first created this topic.
The agency id was numeric field.

@Yacine
Okay. That’s strange. If you’re generating a bad query (divide by zero or similar), then you should be seeing the returned error output from Postgres.
And you don’t see anything in Metabase or Postgres log?
You can change the log level of Metabase: https://metabase.com/docs/latest/operations-guide/start.html#configuring-logging-level

In fact, I have just retested the query and now I have the output that I am dividing by zero. The version I was using was not up to date.

By the way when dividing by Null there is no error message which is quite strange to me.

Thank you for your help Flamber

@Yacine
If you divide by null, then it should make all of it null - well, I think. It’s very late.
It depends on the database maybe.
So this will explode: SELECT 100/0*5
While this will return null: SELECT 100/NULLIF(0,0)*5