By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE FURNITURE
(ROOM VARCHAR2(50) CONSTRAINT PK_DEPT PRIMARY KEY,
APPLIANCE VARCHAR2(50));
BEGIN
INSERT INTO FURNITURE VALUES
('BEDROOM','BED');
INSERT INTO FURNITURE VALUES
('LIVING ROOM', 'SOFA');
INSERT INTO FURNITURE VALUES
('TV ROOM', 'TV');
INSERT INTO FURNITURE VALUES
('ALL', 'LAMP');
END;
/
1 rows affected
SELECT r.room,
f.appliance
FROM (SELECT room FROM furniture WHERE room <> 'ALL') r
INNER JOIN furniture f
ON (r.room = f.room OR f.room = 'ALL')
| ROOM | APPLIANCE |
|---|---|
| BEDROOM | LAMP |
| LIVING ROOM | LAMP |
| TV ROOM | LAMP |
| BEDROOM | BED |
| LIVING ROOM | SOFA |
| TV ROOM | TV |