Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table articles (article_id number, name varchar2(30));
create table tags (tag_id number, parent_tag_id number, name varchar2(30));
create table associations (article_id number, tag_id number);
BEGIN
insert into articles values (1, 'item 1');
insert into articles values (2, 'item 2');
insert into articles values (3, 'item 3');

insert into tags values (100, null, 'parent');
insert into tags values (101, 100, 'child');
insert into tags values (102, 101, 'grandchild');
insert into tags values (103, null, 'another parent');

insert into associations values (1, 102);
insert into associations values (2, 101);
insert into associations values (3, 103);
END;
/
1 rows affected

SELECT r.name AS article_name,
       t.name AS tag_name
FROM   articles r
       INNER JOIN associations a
       ON ( r.article_id = a.article_id )
       CROSS APPLY(
         SELECT name
         FROM   tags t
         START WITH t.tag_id = a.tag_id
         CONNECT BY PRIOR parent_tag_id = tag_id
       ) t


ARTICLE_NAME
TAG_NAME


item 1
grandchild

item 1
child

item 1
parent

item 2
child

item 2
parent

item 3
another parent

ARTICLE_NAME	TAG_NAME
item 1	grandchild
item 1	child
item 1	parent
item 2	child
item 2	parent
item 3	another parent