MariaDB Left join not returning expected results

Question

So im attempting to get a list of table and any foreign constraints that exist on them for the sake of attempting some automated mariabackup restores in python. im running the following query:

SELECT a.TABLE_NAME, b.CONSTRAINT_NAME from information_schema.tables a
LEFT JOIN information_schema.table_constraints b
    ON a.table_name = b.table_name
WHERE a.table_schema = 'world'
    AND b.constraint_type = 'FOREIGN KEY';

My "world" test table, has 3 columns on it - Country (No FK's), CountryLanguage (one fk) and City (one FK). With the above query i'd be expecting 3 tables, 2 returning the FK name and 1 null however the return i get is only the 2 tables with the FK's on them.

I'm sure im missing something but cant quite pin it.

ScaisEdge · Accepted Answer · 2019-03-11 18:55:53Z

1

You should move the left join condition in the ON clause and not in where

    select a.TABLE_NAME
        , b.CONSTRAINT_NAME 
    from information_schema.tables a
    left join information_schema.table_constraints b
        on a.table_name = b.table_name
            and b.constraint_type = 'FOREIGN KEY';
    where a.table_schema = 'world'

If you use a left join table column in where clause this work as an inner join .. so juts extend the ON condition with the others clause for left joined columns

answered Mar 11, 2019 at 18:55

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MariaDB Left join not returning expected results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related