I have two SQL queries. I need to join them into a single SQL query

Solution 1:

If both of them - separately - return result you want (I doubt it; you're using cross-join between t1 and t2), then a simple option is to union them (remove distinct, remove group by (doesn't make sense anyway as you aren't aggregating anything, but distinct does the job) as union replaces both of them in this case):

SELECT order_no,
       status,
       status_cd,
       CASE
          WHEN order_qty - fulfilled_qty > 0 THEN 'inventory-unavailable'
       END
  FROM t1 a, t2 b
 WHERE     a.inventory_stockout = 'TRUE'
       AND a.flag_column = 'TRUE'
       AND status IN ('fulfilled', 'pending')
UNION
SELECT order_no,
       status,
       status_cd,
       CASE
          WHEN order_qty - fulfilled_qty > 0 THEN 'inventory-unavailable'
       END
  FROM t1 a, t2 b
 WHERE     a.inventory_stockout = 'TRUE'
       AND a.flag_column = 'TRUE'
       AND a.subscription_enabled = 'TRUE';

Solution 2:

One question: I see t1 and t2 and synonyms but don't see the join condition or the key logic that joins t1 and t2 together. Do you know what the PK/FK relationship is between those tables?

Suggestion: You're hitting the same table in both with most of the same conditions and same case logic. So, if you combined into a single query, you'd make one pass through the tables which would probably be faster than a UNION. If you add some () to the AND logic, would enable you to do this. Can you try this style and see if it works?

-- Combined Code Mockup

SELECT distinct order_no,status,status_cd,
  CASE when order_qty - fulfilled_qty > 0 then 'inventory-unavailable' END
  FROM t1 a, t2 b
 WHERE t1.PK = t2.FK (Add...)
   AND a.inventory_stockout='TRUE'
   AND a.flag_column='TRUE' 
   AND (status in '(fulfilled','pending' OR (AND a.flag_column='TRUE' 
        AND a.subscription_enabled='TRUE'))
GROUP BY order_no,status,status_cd;

Solution 3:

even if you know which table order_qty came from given you have aliased your two tables a & b it best to always use the alias, to same what you mean.

also having things that look like booleans inventory_stockout & flag_column is bad performance if they truely are string encoded booleans.

Also DISTINCT is a GROUP BY, but given you have DISTINCT you don't need declare the GROUP BY stage, as all the variables are considered. Also as a Snowflake note, this_should_have_a_name is not named in the GROUP BY but it also does not need to be as it is a derivative value from the set of things in the GROUP BY.

I assume you are want the set of the results joined, together, you can UNION them like littlefoot has noted, but this will run a distinct across the two, which is I assume what you would want. Where-as if you know there are zero duplicates in the two result sets, then UNION ALL tells the DB to just concatenate the results together, which is much faster.

The next thing you can do is a CTE and sub select the common parts into one block, and then UNION the sub-results like:

WITH cte AS (
    SELECT DISTINCT 
        order_no, 
        status,
        status_cd,
        IFF( order_qty - fulfilled_qty > 0, 'inventory-unavailable', NULL) AS this_should_have_a_name,
        a.subscription_enabled
    FROM t1 AS a, t2 AS b
    WHERE a.inventory_stockout = 'TRUE'
        AND a.flag_column = 'TRUE' 
)
SELECT order_no, status, status_cd, this_should_have_a_name
FROM cte
WHERE status in '(fulfilled','pending')

UNION 

SELECT order_no, status, status_cd, this_should_have_a_name
FROM cte
WHERE subscription_enabled='TRUE'
;

now we see it written like that, we can just put a OR in there with the CTE

WITH cte AS (
    SELECT DISTINCT 
        order_no, 
        status,
        status_cd,
        IFF( order_qty - fulfilled_qty > 0, 'inventory-unavailable', NULL) AS this_should_have_a_name,
        a.subscription_enabled
    FROM t1 AS a, t2 AS b
    WHERE a.inventory_stockout = 'TRUE'
        AND a.flag_column = 'TRUE' 
)
SELECT order_no, status, status_cd, this_should_have_a_name
FROM cte
WHERE ( status in '(fulfilled','pending')
    OR  subscription_enabled='TRUE' )
;

or without the CTE

SELECT DISTINCT 
        order_no, 
        status,
        status_cd,
        IFF( order_qty - fulfilled_qty > 0, 'inventory-unavailable', NULL) AS this_should_have_a_name
    FROM t1 AS a, t2 AS b
    WHERE a.inventory_stockout = 'TRUE'
        AND a.flag_column = 'TRUE' 
        AND ( status in '(fulfilled','pending') 
            OR a.subscription_enabled='TRUE' )
;

OR's can be slower, and now the logic is merged you the logic needed for the UNION ALL can be seen as

SELECT order_no, status, status_cd, this_should_have_a_name
FROM cte
WHERE status in '(fulfilled','pending')
    AND subscription_enabled != 'TRUE'

UNION ALL 

SELECT order_no, status, status_cd, this_should_have_a_name
FROM cte
WHERE subscription_enabled = 'TRUE'