SQL子查询连接下自定义表列构建的问题及优化建议求助

Let's tackle your two main issues one by one, plus cover practical alternatives to creating physical tables for your custom structures.

Fixing Missing Entries Excluding Associated Data #

The core problem here is almost certainly using an INNER JOIN between your base tables and CustomData. Inner joins only return rows where there’s a matching entry in both tables—so if CustomData lacks an entry for a customEntries_ID, all related rows get dropped entirely.

The fix is to switch to a LEFT JOIN (or LEFT OUTER JOIN; they’re interchangeable in most SQL dialects). This preserves all rows from your base table(s), and fills in NULL values for any CustomData columns where there’s no matching entry.

Example adjusted query:

SELECT 
  ce.customEntries_ID,
  ce.entry_label,
  cd.custom_value -- Will return NULL if no matching CustomData entry exists
FROM 
  CustomEntries ce
LEFT JOIN 
  CustomData cd ON ce.customEntries_ID = cd.customEntries_ID
-- Add WHERE clauses here, but avoid filtering CustomData columns directly unless you explicitly use IS NULL/IS NOT NULL

If you were using subqueries with IN or EXISTS, those will also exclude rows without matches—rewriting them to use left joins will resolve this issue much more cleanly.

Optimizing Query Performance #

Slow subquery-based queries usually stem from poor indexing, overly nested subqueries, or inefficient query structure. Here are actionable fixes:

• Add targeted indexes: Ensure columns used in joins (customEntries_ID) and WHERE clauses have indexes. For CustomData, a composite index on customEntries_ID plus frequently filtered columns can drastically speed up lookups:

  CREATE INDEX idx_customdata_entries ON CustomData(customEntries_ID);
  

• Replace correlated subqueries with joins: Correlated subqueries run once per row, which destroys performance on large datasets. Rewriting them as joins lets the SQL optimizer handle the logic more efficiently.
• Avoid SELECT *: Only fetch the columns you actually need—reducing data transfer and memory usage can make a noticeable difference in query speed.
• Use CTEs for readability and optimization: Common Table Expressions (WITH clauses) make complex queries easier to maintain, and modern optimizers often optimize them as well as (or better than) inline subqueries:

  WITH LatestCustomData AS (
    SELECT customEntries_ID, MAX(custom_value) AS most_recent_value
    FROM CustomData
    GROUP BY customEntries_ID
  )
  SELECT ce.*, lcd.most_recent_value
  FROM CustomEntries ce
  LEFT JOIN LatestCustomData lcd ON ce.customEntries_ID = lcd.customEntries_ID;
  

• Remove unnecessary grouping: If you’re grouping data without needing aggregate functions, strip it out—grouping adds unnecessary overhead.

No Physical Table: Alternatives for Custom Structures #

You don’t need to create a physical table to work with custom column/table structures. Here are the most useful options:

1. Views (Virtual Tables) #

Create a view to define your custom structure—it acts like a table but doesn’t store data (it runs the underlying query every time you access it). Ideal for reusable custom structures:

CREATE VIEW CustomEntryView AS
SELECT 
  ce.customEntries_ID,
  ce.created_date,
  cd1.custom_value AS user_fullname,
  cd2.custom_value AS user_phone
FROM CustomEntries ce
LEFT JOIN CustomData cd1 ON ce.customEntries_ID = cd1.customEntries_ID AND cd1.field_key = 'fullname'
LEFT JOIN CustomData cd2 ON ce.customEntries_ID = cd2.customEntries_ID AND cd2.field_key = 'phone';

-- Use it just like a regular table:
SELECT * FROM CustomEntryView WHERE created_date > '2024-01-01';

2. Common Table Expressions (CTEs) #

For one-off custom structures, use a CTE to define your "custom table" within a single query. It’s temporary and only exists for the duration of the query:

WITH CustomUserProfile AS (
  SELECT 
    customEntries_ID,
    MAX(CASE WHEN field_key = 'fullname' THEN custom_value END) AS fullname,
    MAX(CASE WHEN field_key = 'email' THEN custom_value END) AS email
  FROM CustomData
  GROUP BY customEntries_ID
)
SELECT cup.*, ce.created_date
FROM CustomUserProfile cup
JOIN CustomEntries ce ON cup.customEntries_ID = ce.customEntries_ID;

3. Temporary Tables #

If you need to reuse the custom structure multiple times in a single session, a temporary table works well. It’s stored in temp storage and deleted when your session ends:

CREATE TEMP TABLE TempCustomProfile AS
SELECT 
  ce.customEntries_ID,
  cd.custom_value
FROM CustomEntries ce
LEFT JOIN CustomData cd ON ce.customEntries_ID = cd.customEntries_ID;

-- Use it in subsequent queries:
SELECT * FROM TempCustomProfile WHERE custom_value IS NOT NULL;

4. Dynamic SQL #

For fully dynamic custom structures (where columns or filters change frequently), generate SQL on the fly (either in a stored procedure or your application code). Example for PostgreSQL:

CREATE OR REPLACE FUNCTION get_custom_profile(fields text[])
RETURNS TABLE (customEntries_ID INT, profile JSON) AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT customEntries_ID, json_build_object(%s) AS profile FROM CustomData GROUP BY customEntries_ID',
    array_to_string(ARRAY(SELECT format('%L, custom_value', f) FROM unnest(fields) f), ', ')
  );
END;
$$ LANGUAGE plpgsql;

-- Call it with your desired fields:
SELECT * FROM get_custom_profile(ARRAY['fullname', 'email', 'phone']);

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