Unexpected result with left join in MySQL 8

Question

I'm using MySQL-8.0.23.

I'm trying to get a list of tables without primary keys in schema 'test'. I managed to get the expected result using a "not in" statement (cf. code below). But I' d like to do the same with a left join.

Here are my queries :

--------------
-- LIST ALL TABLES IN SCHEMA 'test'
--------------
select
    t.table_name
from
    information_schema.tables t
where
    t.table_schema = 'test'
and t.table_type = 'BASE TABLE'

+------------------+
| TABLE_NAME       |
+------------------+
| table_with_pk    |
| table_without_pk |
+------------------+



--------------
-- LIST TABLES WITH PRIMARY KEY
--------------
select
    tc.table_name
from
    information_schema.table_constraints tc
where
    tc.table_schema = 'test'
and tc.constraint_type = 'PRIMARY KEY'

+---------------+
| TABLE_NAME    |
+---------------+
| table_with_pk |
+---------------+



--------------
-- LIST TABLES WITHOUT PRIMARY KEY : using "not in" ==> OK
--------------
select
    t.table_name
from
    information_schema.tables t
where
    t.table_schema = 'test'
and t.table_type = 'BASE TABLE'
and t.table_name not in
    (
        select
            tc.table_name
        from
            information_schema.table_constraints tc
        where
            tc.table_schema = 'test'
        and tc.constraint_type = 'PRIMARY KEY'
    )

+------------------+
| TABLE_NAME       |
+------------------+
| table_without_pk |
+------------------+


--------------
-- LIST TABLES WITHOUT PRIMARY KEY : using "left join"   ==> NOK
--------------
select
    t.table_name,
    tc.constraint_type
from
    information_schema.tables t
left join information_schema.table_constraints tc
    on t.table_name = tc.table_name
    and tc.table_schema = t.table_schema
    and tc.constraint_type = 'PRIMARY KEY'
where
    t.table_schema = 'test'
and t.table_type = 'BASE TABLE'

+------------------+-----------------+
| TABLE_NAME       | CONSTRAINT_TYPE |
+------------------+-----------------+
| table_with_pk    | PRIMARY KEY     |
| table_without_pk | PRIMARY KEY     |
+------------------+-----------------+

I was expecting the following result with my last request :

+------------------+-----------------+
| TABLE_NAME       | CONSTRAINT_TYPE |
+------------------+-----------------+
| table_with_pk    | PRIMARY KEY     |
| table_without_pk | NULL            |
+------------------+-----------------+

Can you help me understand what I'm doing wrong ?

Unexpected result with left join in MySQL 8

0 Answers