Reuse Subquery in MySQL

Question

I want to use a CTE to use a subquery in two parts of the query. Unfortunately, MySQL doesn't have CTEs. Is there a way to do this without creating a temporary variable?

I don't have a specific query to simplify. I want to know the general technique. If you need a concrete example, here's one with CTE:

with subquery as (select * from t)
select *, (select count(*) from subquery c) from subquery a, subquery b

What is the equivalent in MySQL?

@JoachimIsaksson I have read the other questions where people simplify for the particular situation without answering this part of the question. I want to reuse the query in a general sense. Thanks. — John Tseng
– John Tseng, Commented Jul 20, 2013 at 19:03

user359040 · Accepted Answer · 2013-07-20 19:06:57Z

1

As far as I am aware, the closest equivalent in MySQL is by creating a view:

create view subquery as select * from t;

select *, (select count(*) from subquery c) from subquery a, subquery b;

Collectives™ on Stack Overflow

Reuse Subquery in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related