如何用SQL查询发往伦敦的订单？通过邮编实现是否可行？

Great question! Using postcode prefixes to filter London orders is absolutely a more reliable approach than searching for "London" directly—free-form address fields are prone to inconsistencies (like users entering district names instead of the city), whereas postcodes follow strict geographic rules that will catch all London-based shipments.

Do you need to use an OR for every postcode prefix? #

You don’t have to write a long string of OR statements (though that works), but you can simplify the query depending on your database’s capabilities:

Option 1: Clean OR chain (works across all SQL databases)

This is straightforward and compatible with every SQL system:

SELECT * 
FROM tt_order_data 
WHERE ship_postcode LIKE 'E%' 
   OR ship_postcode LIKE 'EC%' 
   OR ship_postcode LIKE 'N%' 
   OR ship_postcode LIKE 'NW%' 
   OR ship_postcode LIKE 'SE%' 
   OR ship_postcode LIKE 'SW%' 
   OR ship_postcode LIKE 'W%' 
   OR ship_postcode LIKE 'WC%' 
   OR ship_postcode LIKE 'BR%' 
   OR ship_postcode LIKE 'CR%' 
   OR ship_postcode LIKE 'DA%' 
   OR ship_postcode LIKE 'EN%' 
   OR ship_postcode LIKE 'HA%' 
   OR ship_postcode LIKE 'IG%' 
   OR ship_postcode LIKE 'SL%' 
   OR ship_postcode LIKE 'TN%' 
   OR ship_postcode LIKE 'KT%' 
   OR ship_postcode LIKE 'RM%' 
   OR ship_postcode LIKE 'SM%' 
   OR ship_postcode LIKE 'TW%' 
   OR ship_postcode LIKE 'UB%' 
   OR ship_postcode LIKE 'WD%' 
   OR ship_postcode LIKE 'CM%';

Option 2: Regular expressions (for databases that support it)

If your database supports regex (like MySQL, PostgreSQL, SQL Server 2016+), you can condense the logic into a single pattern:

-- MySQL example
SELECT * 
FROM tt_order_data 
WHERE ship_postcode REGEXP '^(E|EC|N|NW|SE|SW|W|WC|BR|CR|DA|EN|HA|IG|SL|TN|KT|RM|SM|TW|UB|WD|CM)';

-- PostgreSQL example
SELECT * 
FROM tt_order_data 
WHERE ship_postcode ~ '^(E|EC|N|NW|SE|SW|W|WC|BR|CR|DA|EN|HA|IG|SL|TN|KT|RM|SM|TW|UB|WD|CM)';

Option 3: Helper table (best for long-term maintenance)

If you expect the London postcode range to change, or want to keep your main query clean, create a helper table to store valid prefixes:

-- Create and populate the helper table
CREATE TABLE london_postcode_prefixes (prefix VARCHAR(2) NOT NULL PRIMARY KEY);
INSERT INTO london_postcode_prefixes VALUES 
('E'), ('EC'), ('N'), ('NW'), ('SE'), ('SW'), ('W'), ('WC'),
('BR'), ('CR'), ('DA'), ('EN'), ('HA'), ('IG'), ('SL'), ('TN'),
('KT'), ('RM'), ('SM'), ('TW'), ('UB'), ('WD'), ('CM');

-- Query using a JOIN
SELECT o.* 
FROM tt_order_data o
JOIN london_postcode_prefixes l
  ON o.ship_postcode LIKE CONCAT(l.prefix, '%');

Key tips for better performance & accuracy #

• Case insensitivity: Some databases treat LIKE as case-sensitive. To avoid missing entries (e.g., "e1" instead of "E1"), convert the postcode to lowercase first:

  WHERE LOWER(ship_postcode) LIKE 'e%'
  

• Indexing: If your tt_order_data table is large, add an index on ship_postcode—prefix matching (LIKE 'X%') can leverage this index to speed up the query significantly.
• Edge cases: Double-check if any postcodes in your dataset have leading spaces; if so, use TRIM(ship_postcode) to clean them before matching.

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