CODEWITHSUNDEEP
mysqloracle-databasecommon-table-expression
Madhav
Madhav

Reputation: 2415

WITH clause in MySQL?

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

Upvotes: 4

Views: 8262

Answers (4)

Rasht Nightmare
Rasht Nightmare

Reputation: 1

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 !!

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37049

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

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

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

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

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.

Upvotes: 1

Related Questions