WITH bus_t AS ( SELECT 'A' bus_cd, 10 seat_cnt ) , passenger_t AS ( SELECT 'A' passenger_id, 'A' bus_cd, 1 seat_no UNION ALL SELECT 'B', 'A', 3 UNION ALL SELECT 'C', 'A', 10 ) , bus_tmp AS ( SELECT bus_cd, seat_cnt , 1 seat_no FROM bus_t WHERE bus_cd = 'A' UNION ALL SELECT bus_cd , seat_cnt , seat_no + 1 FROM bus_tmp WHERE seat_no < seat_cnt ) SELECT a.* FROM bus_tmp a LEFT OUTER JOIN passenger_t b ON a.bus_cd = b.bus_cd AND a.seat_no = b.seat_no WHERE b.bus_cd IS NULL ;