如何在locations表无country_name字段时按country_name排序查询？

Got it, let's work through this problem. You need to sort your query results from the locations table using country_name, but that field lives in the countries table instead—since the two tables are linked via country_id, we can leverage that relationship to make this work.

First, let's recap the table structures for reference:

-- locations table schema
Name          Null?    Type
-------------- -------- ------------
LOCATION_ID   NOT NULL NUMBER(4)
STREET_ADDRESS          VARCHAR2(40)
POSTAL_CODE             VARCHAR2(12)
CITY          NOT NULL VARCHAR2(30)
STATE_PROVINCE          VARCHAR2(25)
COUNTRY_ID              CHAR(2)

-- countries table schema
Name          Null?    Type
------------ -------- ------------
COUNTRY_ID   NOT NULL CHAR(2)
COUNTRY_NAME           VARCHAR2(40)
REGION_ID              NUMBER

Approach 1: Correlated Subquery in ORDER BY #

The example you provided uses this method, and it works perfectly. Here's the code again, with a quick breakdown:

SELECT country_id, city, state_province 
FROM locations l 
ORDER BY (SELECT country_name FROM countries c WHERE l.country_id = c.country_id);

For every row in locations, this subquery pulls the matching country_name from countries using the shared country_id. The database then uses those fetched country_name values to sort your final result set.

One thing to note: If any country_id in locations doesn't have a match in countries, the subquery returns NULL. Depending on your database's settings, these rows will either sort to the top or bottom of your results.

Approach 2: Join the Tables (Recommended) #

Using a JOIN is typically more readable and can perform better with large datasets compared to correlated subqueries. Here's how to do it:

SELECT l.country_id, l.city, l.state_province
FROM locations l
INNER JOIN countries c ON l.country_id = c.country_id
ORDER BY c.country_name;

This joins the two tables on country_id, so we can directly reference c.country_name in the ORDER BY clause.

• If you want to include locations that don't have a matching country (with NULL for country_name), swap INNER JOIN for LEFT JOIN.
• If you want to see the country_name in your output, just add c.country_name to the SELECT clause.

内容的提问来源于stack exchange，提问作者Xiaomeng Su