Question 20115 in Mysql Question posted in
The official MySQL documentation can be found here.

Flag the Match type of a query in results without running subquery twice – Mysql

I have a complex query. The following below is the most basic example of what I want to do.

SELECT 
  match_by_color AS match_type,
  t1.*
FROM t1 
WHERE 
  ( t1.color LIKE 'color%') -- match_by_color
  OR (t1.type in (select type from t2)) -- match_by_type
  OR (t1.name in (select name from t3 WHERE ...)) -- match_by_name
  OR (t1.age in (select age from t4 WHERE ...)) -- match_by_age

I have 3-4 conditions for matching a record. And if it matches on one of the conditions I want to identify the first matching type without running the sub queries again. I could do something like this but I feel like it would be running the same queries twice.

-- I could do this but it would be running the conditional sub queries twice
(
 CASE
   WHEN ( t1.color LIKE 'color%') THEN match_by_color
   WHEN (t1.type in (select type from t2)) THEN match_by_color
   WHEN (t1.name in (select name from t3 WHERE ...)) THEN match_by_name
   WHEN (t1.age in (select age from t4 WHERE ...)) THEN match_by_age
   ELSE no_match
 END
) AS match_type
Tags:

2

Answers


  1. 0

    try this, I followed @nbk advice I also removed all the where clause to simplify, I guess it will perform better this way.

    ;WITH 
  match_by_type AS (
    SELECT type FROM t2
  ),
  match_by_name AS (
    SELECT name FROM t3 WHERE ...
  ),
  match_by_age AS (
    SELECT age FROM t4 WHERE ...
  )

SELECT *
FROM (
  SELECT 
    CASE
      WHEN (t1.color LIKE 'color%') THEN 'match_by_color'
      WHEN (t1.type IN (SELECT type FROM match_by_type)) THEN 'match_by_type'
      WHEN (t1.name IN (SELECT name FROM match_by_name)) THEN 'match_by_name'
      WHEN (t1.age IN (SELECT age FROM match_by_age)) THEN 'match_by_age'
      ELSE 'no_match'
    END AS match_type,
    t1.*
  FROM t1 
) subquery
WHERE 
  subquery.match_type <> 'no_match'
    Login or Signup to reply.
  2. 0

    The problem with oversimplified questions about a "complex query" is that we are not aware of the complexities, which will inevitably have an impact on the best way to approach a query.

    If the relationships between t1 and each of t2, t3 and t4 return 0 or 1 row then it is worth testing LEFT JOIN:

    SELECT 
    CASE
        WHEN t1.color LIKE 'color%' THEN 'match_by_color'
        WHEN t2.type IS NOT NULL THEN 'match_by_type'
        WHEN t3.name IS NOT NULL THEN 'match_by_name'
        WHEN t4.age  IS NOT NULL THEN 'match_by_age'
    END AS match_type,
    t1.*
FROM t1
LEFT JOIN t2 ON t1.type = t2.type
LEFT JOIN t3 ON t1.name = t3.name -- add rest of join criteria here
LEFT JOIN t4 ON t1.age  = t4.age  -- add rest of join criteria here
WHERE t1.color LIKE 'color%' -- match_by_color
   OR t2.type IS NOT NULL    -- match_by_type
   OR t3.name IS NOT NULL    -- match_by_name
   OR t4.age  IS NOT NULL    -- match_by_age
;

    If the relationships may return more than 1 row, then try using EXISTS(...):

    SELECT 
    CASE
        WHEN t1.color LIKE 'color%' THEN 'match_by_color'
        WHEN EXISTS(SELECT 1 FROM t2 WHERE type = t1.type) THEN 'match_by_type'
        WHEN EXISTS(SELECT 1 FROM t3 WHERE name = t1.name) THEN 'match_by_name'
        WHEN EXISTS(SELECT 1 FROM t4 WHERE age  = t1.age ) THEN 'match_by_age'
    END AS match_type,
    t1.*
FROM t1 
WHERE t1.color LIKE 'color%' -- match_by_color
   OR EXISTS(SELECT 1 FROM t2 WHERE type = t1.type) -- match_by_type
   OR EXISTS(SELECT 1 FROM t3 WHERE name = t1.name) -- match_by_name
   OR EXISTS(SELECT 1 FROM t4 WHERE age  = t1.age ) -- match_by_age
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search