SQL to retrieve column info including references

Question

I'm trying to query the database schema to retrieve information about the column configuration. Included in the result set I want a list of foreign keys which are referencing a column. Hopefully somebody here may spot an error in my query, which always returns an empty set.

SELECT

C.ORDINAL_POSITION          AS `position`,
C.COLUMN_DEFAULT            AS `default`,
C.IS_NULLABLE               AS `nullable`,
C.CHARACTER_MAXIMUM_LENGTH  AS `max`,
C.NUMERIC_PRECISION         AS `size`,
C.NUMERIC_SCALE             AS `scale`,
C.COLUMN_TYPE               AS `type`,
U.TABLE_NAME                AS `referenced`,

CASE C.COLUMN_NAME WHEN NULL
  THEN U.REFERENCED_COLUMN_NAME
  ELSE C.COLUMN_NAME
END AS `name`

 FROM INFORMATION_SCHEMA.COLUMNS AS C
 RIGHT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS U
   ON U.REFERENCED_TABLE_SCHEMA = C.TABLE_SCHEMA
   AND U.REFERENCED_TABLE_NAME = C.TABLE_NAME
 WHERE C.TABLE_NAME = 'my_table'
   AND C.TABLE_SCHEMA = 'my_database'
 ORDER BY `position` ASC

Answer 1

You need a LEFT JOIN:

SELECT

    C.ORDINAL_POSITION          AS `position`,
    C.COLUMN_NAME               AS `name`
    C.COLUMN_DEFAULT            AS `default`,
    C.IS_NULLABLE               AS `nullable`,
    C.CHARACTER_MAXIMUM_LENGTH  AS `max`,
    C.NUMERIC_PRECISION         AS `size`,
    C.NUMERIC_SCALE             AS `scale`,
    C.COLUMN_TYPE               AS `type`,
    U.TABLE_NAME                AS `referencing_table`,
    U.COLUMN_NAME               AS `referencing_column`

 FROM INFORMATION_SCHEMA.COLUMNS AS C
 LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS U
   ON U.REFERENCED_TABLE_SCHEMA = C.TABLE_SCHEMA
  AND U.REFERENCED_TABLE_NAME = C.TABLE_NAME
 WHERE C.TABLE_NAME = 'my_table'
   AND C.TABLE_SCHEMA = 'my_database'
 ORDER BY `position` ASC

A LEFT JOIN returns all the rows that an INNER JOIN would return. In addition, it returns any rows in the left table (COLUMNS) that have no match, with NULL values for the columns from the right table.