mysql - Trying to get around lack of CTE

Question

I'm having some issues filtering the results from a longer query I have.

Suppose we have three columns, with some sample rows:

# column_1, column_2, column_3

'value 1', 'value 2', null
'value 1', 'value 2', '2'
'value 2', 'value 2', null

I'd like to select all rows but exclude those where column_3 is null and its column_1, column_2 values are associated with at least one non-null column_3 value.

A pain point is that I'm stuck with MySQL 5.7, and these rows are produced by a pretty text-lengthy subquery. If I had CTEs I could do:

WITH 
    cte1 AS (...)
SELECT *
FROM cte1 as a
WHERE
    column_3 IS NOT NULL
    OR NOT EXISTS (
        SELECT 1
        FROM cte1 as b
        WHERE
            b.column_1 = a.column_1
            AND b.column_2 = a.column_2
            AND b.column_3 IS NOT NULL
    )

Is there some way I can do this without copy pasting the large subquery, given that I don't have access to CTEs?

question from:https://stackoverflow.com/questions/65894243/trying-to-get-around-lack-of-cte

Answer 1

With or without CTE's, you would need to copy paste the large subquery. With would be:

with a as (subquery)
select *
from a
where ...

Without is even shorter:

select *
from (subquery) as a
where ...