CODEWITHSUNDEEP
mysqlsql
stefmikhail
stefmikhail

Reputation: 7125

Combine Three SQL Queries

I have the following three SQL queries. I would like to combine them into one is possible, either using the current method, or another method if it would be more efficient.

NOTE: I need to know the distinct count values for all three WHERE clauses.

Query 1:

SElECT COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight;

QUERY 2:

SElECT COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight != $maxImageHeight;

QUERY 3:

SElECT COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight;

Also, my database is MySQL. I have heard that using != for "not equal" doesn't work in all cases, but that <> is better. In my case, should != be fine?

Upvotes: 1

Views: 192

Answers (3)

srivani
srivani

Reputation: 1022

Do you have indexes on primaryId in your primary_images and secondary_images tables? If yes, then you should push the where clause inside so that the indexes can be used. 

SElECT COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images WHERE imgId = $imgId AND imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight 
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images WHERE primaryId = $imgId AND imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight
) AS union_table

Otherwise it will be a full table scan to find the imgId as union_table is a temporary table created by Mysql and doesn't have any indexes.

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

I believe that a CASE and a GROUP BY are needed:

SELECT CASE
       WHEN imgWidth  = $maxImageWidth AND imgHeight  = $maxImageHeight
       THEN 0
       WHEN imgWidth != $maxImageWidth AND imgHeight  = $maxImageHeight
       THEN 1
       WHEN imgWidth  = $maxImageWidth AND imgHeight != $maxImageHeight
       THEN 2
       END AS count_type,
       COUNT(*) AS imgCount
  FROM (SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
        UNION ALL 
        SELECT imgHeight, imgWidth, primaryId FROM secondary_images
       ) AS union_table
 WHERE primaryId = $imgId
   AND (imgWidth = $maxImageWidth OR imgHeight = $maxImageHeight)
 GROUP BY count_type;

It is at least tempting to push the WHERE clause down into the UNION subqueries, but the optimizer should do that anyway. Do check that it does do that by studying the EXPLAIN output. If the optimizer does not do the predicate pushdown automatically, you'd write:

SELECT CASE
       WHEN imgWidth  = $maxImageWidth AND imgHeight  = $maxImageHeight
       THEN 0
       WHEN imgWidth != $maxImageWidth AND imgHeight  = $maxImageHeight
       THEN 1
       WHEN imgWidth  = $maxImageWidth AND imgHeight != $maxImageHeight
       THEN 2
       END AS count_type,
       COUNT(*) AS imgCount
  FROM (SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
         WHERE primaryId = $imgId  -- or: imgId = $imgId
           AND (imgWidth = $maxImageWidth OR imgHeight = $maxImageHeight)
        UNION ALL 
        SELECT imgHeight, imgWidth, primaryId FROM secondary_images
         WHERE primaryId = $imgId
           AND (imgWidth = $maxImageWidth OR imgHeight = $maxImageHeight)
       ) AS union_table
 GROUP BY count_type;

The disadvantage of this solution as written is that the $maxImageWidth and $maxImageHeight variables are used 4 times each (and $imgId once). If you are preparing the statement as shown (embedding the values in the string representing the SQL), it isn't too bad. If you are using placeholders, you need a naming scheme for the placeholders if possible (:img_ht, :img_wd, :img_id (or at least :1, :2, :3) in place of the ? placeholders) so you only pass each value once in the EXECUTE statement. That capability depends on your DBMS, though; MySQL is one of a number of DBMS that do not support this facility.

Upvotes: 2

Kibbee
Kibbee

Reputation: 66112

This might work for you.

SElECT COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId 
AND (imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight
OR imgWidth = $maxImageWidth AND imgHeight != $maxImageHeight
OR imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight);

I also believe this is equivalent, although I'm not sure.

SElECT COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId 
AND (imgWidth = $maxImageWidth OR imgHeight = $maxImageHeight);

As per the request, here is a single query that returns 3 rows.

SELECT 'ALL EQUAL' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight
UNION ALL
SELECT 'WIDTH EQUAL' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight != $maxImageHeight;
UNION ALL
SELECT 'HEIGHT EQUAL' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images  
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight;

Upvotes: 6

Related Questions