Assuming that the data isn't duplicated (so you don't have a row for both MI/IN and IN/MI) you can unpivot that table directly to get all the individual border lengths for each state:

select *
from borders
unpivot (code for code_idx in (code1 as 1, code2 as 2)) u

and join that to the states table to get the matching names:

...
join state s on s.code = u.code

then use the sum() aggregate function to get the total length for each state:

select s.name, sum(u.length)
from borders
unpivot (code for code_idx in (code1 as 1, code2 as 2)) u
join state s on s.code = u.code
group by s.name

and then order the result by descending total length, and pick the first row (i.e. the one with the highest value):

select s.name
from borders
unpivot (code for code_idx in (code1 as 1, code2 as 2)) u
join state s on s.code = u.code
group by s.name
order by sum(u.length) desc
fetch first 1 row only

fiddle showing the intermediate steps, including how to get the name and total length if you want both.
If you might have ties then you need to decide whether to pick one of them - indeterminately, which this query will do, or determinately by adding say the state code to the order-by clause; or show them all - changing only to with ties .
If you're on an older version of Oracle that doesn't support the fetch syntax you can put the query in an inline view and select the first row from that.
The query i have made so far. ...
That would also work with a bit of tweaking, particularly changing your invalid where clause to an order by ; but you don't need to use two subqueries as you suggested, you can modify the join condition to look at both columns in the borders table:

on (s.code = b.code1 or s.code = b.code2)

and then as above, order by the total length and fetch the first value:

select s.name
from state s
join borders b
on (s.code = b.code1 or s.code = b.code2)
group by s.name
order by sum(b.length) desc
fetch first 1 row only;

fiddle

您可以对borders表执行UNPIVOT操作，然后计算每个状态的总长度并找到最大长度，然后连接到state表以查找名称：

SELECT s.name
FROM   state s
       INNER JOIN (
         SELECT code,
                SUM(length) AS total_length
         FROM   borders
         UNPIVOT (
           code FOR key IN (code1, code2)
         )
         GROUP BY code
         ORDER BY SUM(length) DESC
         FETCH FIRST ROW WITH TIES
       ) b
       ON s.code = b.code

其中，对于示例数据：

CREATE TABLE state (name, code) AS
SELECT 'Michigan',  'MI' FROM DUAL UNION ALL
SELECT 'Indiana',   'IN' FROM DUAL UNION ALL
SELECT 'Illinois',  'IL' FROM DUAL UNION ALL
SELECT 'Ohio',      'OH' FROM DUAL UNION ALL
SELECT 'Wisconsin', 'WI' FROM DUAL;

CREATE TABLE borders (code1, code2, length) AS
SELECT 'MI', 'IN', 20 FROM DUAL UNION ALL
SELECT 'IL', 'IN', 50 FROM DUAL UNION ALL
SELECT 'MI', 'OH',  5 FROM DUAL UNION ALL
SELECT 'IN', 'OH', 40 FROM DUAL UNION ALL
SELECT 'WI', 'MI', 30 FROM DUAL;

输出：
| 名称|
| - -|
| 印第安纳州|
fiddle

也许一个更简单的方法是在CTE中进行联合。我不知道SQL的Oracle方言，但这应该是标准的，我们很容易做到：

with state_borders as
(
   select code1 as code, length from border
   union all select code2 as code, lenght from border
)
select top 1
   s.name,
   sum(b.length) as border_length
from
   state_borders as b
   left join state as s on s.code = b.code
group by
   s.name
order by
   sum(b.length) desc;

未测试。
注意不要在数据库中使用保留字作为名称，例如名称和州。