4

Does MySQL support common table expressions? For example in Oracle there's the WITH clause? :

WITH aliasname
AS
  ( SELECT COUNT(*) FROM table_name )
SELECT COUNT(*) FROM dept,aliasname
APC
  • 144,005
  • 19
  • 170
  • 281
Madhav
  • 2,285
  • 3
  • 17
  • 16
  • `AS` is used for alias name don't know about oracle. What is your question exactly? – Bhavik Shah Feb 07 '13 at 09:58
  • @BhavikShah - they're asking about support for common table expressions in MySQL – APC Feb 07 '13 at 10:40
  • No - see [How do you use the "WITH" clause in MySQL?](http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql) –  Feb 07 '13 at 10:43
  • MySql 8.0 introduces CTEs: https://dev.mysql.com/blog-archive/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/ – Onno Rouast Apr 18 '22 at 07:40

4 Answers4

2
SELECT t.name,
       t.num
  FROM TABLE t
  JOIN (SELECT c.id,COUNT(*) 'num1'
          FROM TABLE1 c
         WHERE c.column = 'a'
      GROUP BY c.id) ta1 ON ta1.id = t.id
 JOIN (SELECT d.id,COUNT(*) 'num2'
          FROM TABLE2 d
         WHERE d.column = 'a'
      GROUP BY d.id) ta2 ON ta2.id = t.id
Vikram Jain
  • 5,498
  • 1
  • 19
  • 31
1

One way is to use a subquery:

  SELECT COUNT(*) 
  FROM dept,
  ( 
      SELECT COUNT(*) 
      FROM table_name
  ) AS aliasname

Note that the , between the two tables will cross join the two tables the same as in your query you posted. IF there is any relation between them you can JOIN them instead.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • Is it WITH clause in mysql ? – Madhav Feb 07 '13 at 10:00
  • @Madhav No there is no `WITH` in MySQL, as I understand from the question, he needs something like Oracle CTE which is defined using the `WITH` clause, the alternative way in mysql is to use a subquery. – Mahmoud Gamal Feb 07 '13 at 10:02
1

No, MySQL does not support Common Table Expressions (CTE). So instead of using WITH tablealias as (....), you will have to do a subquery.

For example,

WITH totalcount AS 
(select userid, count(*) as tot from logins group by userid)
SELECT a.firstname, a.lastname, b.tot
FROM users a
INNER JOIN 
totalcount b 
    on a.userid = b.userid

can be re-written in MySQL as

SELECT a.firstname, a.lastname, b.totalcount
FROM users a
INNER JOIN 
(select userid, count(*) as tot from logins group by userid) b 
    on a.userid = b.userid
zedfoxus
  • 35,121
  • 5
  • 64
  • 63
0

So let's talk about WITH clause .

WITH clause and INNER JOIN otherwise JOIN are a kind of same , but WITH clause gives you much more latitude especially in WHERE clause ;

I am going to make a view that'll get values like count of users , user name and etc.

First (Creating our tables users and inserted_users) :

inserted_users table :

 CREATE TABLE users (id BIGINT(10) AUTO INCEREMENT PRIMARY KEY , name VARCHAR(50))    

users table :

CREATE TABLE users (id BIGINT(10) AUTO INCEREMENT PRIMARY KEY , name VARCHAR(50) , gender TINYINT(1))

Second (Inserting some values to work with) :

users table :

INSERT INTO users (name,gender) VALUES ('Abolfazl M' , 1)

I don't want to insert into inserted_users by query , but I want to add a TRUGGER which will insert data automatically to users_inserted table before data be inserted into users table.

Third (Creating trigger add_uinserted) :

DELIMITER $$


CREATE TRIGGER IF NOT EXISTS add_uinserted BEFORE INSERT ON users FOR EACH ROW 
BEGIN 

IF NEW.name <> '' THEN 

     INSERT INTO users_inserted (name) VALUES (NEW.name);

ELSE 

INSERT INTO users (name,gender) VALUES ('Unknown',NEW.gender);
INSERT INTO users_inserted (name) VALUES ('Unknown');

END IF;

END$$

DELIMITER ;

Run the query and the trigger will be created and at last let's create a view to give us result from a query having WITH clause .

CREATE VIEW select_users AS

WITH GetCAll AS (
SELECT u1.id As Uid ,COUNT(u1.name) AS CnAll FROM users u1 
)

SELECT u1.name AS NAME,CASE 
WHEN s1.gender = 1 THEN "MALE"
WHEN s1.gender = 0 THEN "FEMALE"
ELSE "UNKNOWN"
END AS GENDER,CASE 
WHEN u1.id = gca.Uid THEN "INSERTED TO users_inserted"
ELSE "NOT INSERTED TO users_inserted"
END AS INSERTED,gca.CnAll FROM GetCAll AS gca INNER JOIN users u1;

After you query got ran the view will be created and by calling the view select_users the data will be shown

Last step (calling the select_users view) :

SELECT * FROM select_users 

Thanks for taking a look at my answer , and that's it !!