CODEWITHSUNDEEP
mysql
John Tseng
John Tseng

Reputation: 6352

Reuse Subquery in MySQL

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?

Upvotes: 1

Views: 1354

Answers (1)

user359040
user359040

Reputation:

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;

SQLFiddle here.

Upvotes: 1

Related Questions