PostgreSQL Natural Join is not working for two related tables

Question

I've got the following design of the two tables that I am having trouble joining using NATURAL JOIN to test the referential integrity. It works with INNER JOIN, however it doesn't with NATURAL JOIN :

Table 1. Project

        DROP TABLE IF EXISTS Project CASCADE;
        CREATE TABLE Project (
                ProjectKey CHAR(10)
                ,ProjectName VARCHAR(255)
                ,ProjectType CHAR(20)
                ,URL VARCHAR
                ,ProjectLeadUsername VARCHAR(255) NOT NULL
                ,ProjectLeadGroupName VARCHAR(255) NOT NULL
                ,DefaultAssignee CHAR(50)
                ,ProjectCategory VARCHAR(255) NULL
                ,Description TEXT
                ,PermissionSchemeKey CHAR(10)
                ,IssueTypeSchemeKey CHAR(20)
                ,WorkflowSchemeKey CHAR(20)

            ,CONSTRAINT Project_PK PRIMARY KEY (ProjectKey)

            ,CONSTRAINT Project_FK_PermissionScheme FOREIGN KEY (PermissionSchemeKey) REFERENCES PermissionScheme
                ON DELETE RESTRICT
                ON UPDATE CASCADE

            ,CONSTRAINT Project_FK_IssueTypeScheme FOREIGN KEY (IssueTypeSchemeKey) REFERENCES IssueTypeScheme
                ON DELETE RESTRICT
                ON UPDATE CASCADE

            ,CONSTRAINT Project_FK_WorkflowScheme FOREIGN KEY (WorkflowSchemeKey) REFERENCES WorkflowScheme
                ON DELETE RESTRICT
                ON UPDATE CASCADE

            ,CONSTRAINT Project_FK_User FOREIGN KEY (ProjectLeadUsername, ProjectLeadGroupName) REFERENCES JIRA_User
                ON DELETE RESTRICT
                ON UPDATE CASCADE

            ,CONSTRAINT Project_DI_DefaultAssignee CHECK (DefaultAssignee IN ('Project Lead', 'Unassigned'))

            ,CONSTRAINT Project_DI_ProjectType CHECK (ProjectType IN ('Software', 'Business'))

        );

Table 2. Permission Scheme

        DROP TABLE IF EXISTS PermissionScheme CASCADE;
        CREATE TABLE PermissionScheme (
                PermissionSchemeKey CHAR(10)
                ,PermissionSchemeName VARCHAR(255)
                ,Description TEXT

            ,CONSTRAINT PermissionScheme_PK PRIMARY KEY(PermissionSchemeKey)
        );

Query 1. The NATURAL JOIN query that isn't working (return no records at all)

        SELECT pr.ProjectKey
                ,pr.ProjectName
                ,pr.ProjectType
                ,ps.PermissionSchemeKey
                ,ps.PermissionSchemeName
                ,ps.Description  
        FROM Project pr NATURAL JOIN PermissionScheme ps

Query 2. INNER JOIN

            SELECT pr.ProjectKey
                    ,pr.ProjectName
                    ,pr.ProjectType
                    ,ps.PermissionSchemeKey
                    ,ps.PermissionSchemeName
                    ,ps.Description  
            FROM Project pr 
                INNER JOIN PermissionScheme ps
                    ON pr.PermissionSchemeKey = ps.PermissionSchemeKey

Using INNER JOIN query, it returns the desired result set. However,when using the NATURAL JOIN it returns no records at all.

Answer 1

The reason NATURAL JOIN won't work here is because both your tables also have a Description column in addition to the PermissionSchemeKey column, so postgres will try to inner join on both columns.

i.e.

ON pr.PermissionSchemeKey = ps.PermissionSchemeKey
AND pr.Description = ps.Description