UNION ALL查询无结果/结果重复或展示异常的技术求助

Hey there! Let's break down your issues with combining the images and videos tables using UNION ALL step by step.

Issue 1: No results when keeping videos.description AS video_description, abnormal display when removing it #

The core problem here is how UNION ALL works—it requires the same number of columns, matching data types, and consistent column order in both subqueries. When you add video_description to the videos query but don't include a corresponding column in the images query, the database can't align the results properly, leading to no output or mismatched data when you remove the field.

Fix this by adding a placeholder column for the missing field in the images subquery (use NULL since there's no video description for image records):

-- Properly aligned UNION ALL query
SELECT
  id,
  title,
  description AS image_description,
  NULL AS video_description,
  'image' AS content_type, -- Helps distinguish media types on frontend
  created_at
FROM images
UNION ALL
SELECT
  id,
  title,
  NULL AS image_description,
  description AS video_description,
  'video' AS content_type,
  created_at
FROM videos
ORDER BY created_at DESC; -- Mixed sorting by creation time

By adding NULL AS video_description to the images query, you ensure both subqueries have identical column counts and compatible data types. The content_type field also makes it easier to handle display logic (like showing an image vs video player) on your page.

Issue 2: Occasional duplicate results #

UNION ALL is intentionally designed to preserve all rows from both queries, including duplicates if rows are identical across all selected columns. Here are two common fixes:

1. Switch to UNION instead of UNION ALL: This automatically removes duplicate rows, but note that UNION sorts results to deduplicate, which can be slower on large datasets:

   SELECT ... FROM images
   UNION -- No ALL keyword here
   SELECT ... FROM videos
   ORDER BY created_at DESC;
   

2. Filter business-specific duplicates: If duplicates stem from the same content existing in both tables (e.g., a video with a thumbnail in images), add a condition to exclude overlapping records. For example, if you have a shared media_id field:

   SELECT ... FROM images WHERE media_id NOT IN (SELECT media_id FROM videos)
   UNION ALL
   SELECT ... FROM videos;
   

Also, double-check your selected columns—if you're not including a unique identifier or content_type, rows from different tables that look similar might appear as duplicates. The content_type field we added earlier helps you verify if a "duplicate" is actually an image and video of the same content.

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