【Oracle DB】NATURAL JOINの使いどころ

はじめに

SQLのテーブル結合方法にはNATURAL JOIN（自然結合）がありますが、この結合方法を実践で使う機会は少ないと思います。
システム内で実行するSQLを書く場合は、後日テーブルへカラムが追加される可能性も加味した上で、拡張にはオープンであるべき原則¹に従うとNATURAL JOINを使うべきではありません。

ですが、Oracle DBの場合はデータ・ディクショナリ²を検索するときにNATURAL JOINを活用できますので、事例をサンプルクエリとともにメモします。

環境

Oracle Database 19c

事例1 : テーブルの物理名と論理名を検索

クエリ例

SELECT
    table_name
   ,comments
   ,num_rows
   ,last_analyzed
FROM
    user_tables
NATURAL JOIN
    user_tab_comments
ORDER BY
    table_name
;

検索結果例

TABLE_NAME	COMMENTS	NUM_ROWS	LAST_ANALYZED
TABLE_A	テーブルA	100	2024-10-02 00:00:00
TABLE_B	テーブルB	2,000	2024-10-01 00:00:00
TABLE_C	テーブルC	40,000	2024-04-01 00:00:00

データディクショナリ「user_tab_comments」はCOMMENT ON TABLEを実行していないテーブルの情報も持っているので、外部結合する必要はありません。

事例2 : テーブルのカラム定義と論理名を検索

クエリ例

SELECT
    table_name
   ,column_name
   ,comments
   ,data_type
   ,data_length
   ,data_precision
   ,data_scale
   ,nullable
   ,char_used
FROM
    user_tab_columns
NATURAL JOIN
    user_col_comments
WHERE 1=1
AND table_name = :table_name
ORDER BY
    column_id
;

検索結果例

TABLE_NAME	COLUMN_NAME	COMMENTS	DATA_TYPE	DATA_LENGTH	DATA_PRECISION	DATA_SCALE	NULLABLE	CHAR_USED
TABLE_A	ID	識別子	NUMBER	22	10	0	N
TABLE_A	TXT_COL_A	文字カラムA	VARCHAR2	400			Y	B
TABLE_A	TXT_COL_B	文字カラムB	VARCHAR2	400			Y	C
TABLE_A	TXT_COL_C	文字カラムC	CHAR	1			N	C
TABLE_A	NUM_COL_D	数値カラムD	NUMBER	22	6	3	N
TABLE_A	NUM_COL_E	数値カラムE	NUMBER	22			Y
TABLE_A	DATE_COL_F	日付カラムF	DATE	7			N

データディクショナリ「user_col_comments」はCOMMENT ON COLUMNを実行していないカラムの情報も持っているので、外部結合する必要はありません。

事例3 : INDEXが貼られている項目の検索

クエリ例

SELECT
    table_name
   ,index_name
   ,index_type
   ,uniqueness
   ,constraint_index
   ,column_name
FROM
    user_indexes
NATURAL JOIN
    user_ind_columns
WHERE 1=1
AND table_name = :table_name
ORDER BY
    index_name
   ,column_position
;

検索結果例

TABLE_NAME	INDEX_NAME	INDEX_TYPE	UNIQUENESS	CONSTRAINT_INDEX	COLUMN_NAME
TABLE_A	IND_A_01	NORMAL	NONUNIQUE	NO	TXT_COL_A
TABLE_A	IND_A_02	BITMAP	NONUNIQUE	NO	TXT_COL_C
TABLE_A	IND_A_03	NORMAL	NONUNIQUE	NO	DATE_COL_F
TABLE_A	IND_A_03	NORMAL	NONUNIQUE	NO	NUM_COL_D
TABLE_A	PK_A	NORMAL	UNIQUE	YES	ID

補足

本例ではUSERビューに対するクエリを記載しましたが、ALLビューやDBAビューでも同様にNATURAL JOINが使えます。

参考

1. SOLID原則の内、"O"にあたる「オープン・クローズドの原則」。 ↩

2. Oracle DB内にあるオブジェクトのメタデータを参照できるビューのこと。厳密には「静的データ・ディクショナリ・ビュー」 ↩