2

I would like to perform the following. This IS ILLEGAL in MySQL. The associated CTE in PostGRESQL (a "with" clause) DOES WORK. The assumption here is the subqueries in MySQL are not fully-qualified CTE.

Please note: this query obviously is very silly, it has just been sanitized and shortened for your viewing pleasure and to succinctly highlight the issue.

Please note also that the "get a symmetric difference" task is orthogonal to the question at hand.

SELECT A.val
FROM
  (SELECT val FROM tableA) AS A,
  (SELECT val FROM tableB) AS B
WHERE (A.val NOT IN (SELECT val FROM B)
       OR B.val NOT IN (SELECT val FROM A));

The PostGRES example (frankly not tested, as I don't have a PostGRES database to test against at the moment, but I'm 100% sure it would work...I've done similar things in the past):

WITH A as (SELECT val FROM tableA),
     B as (SELECT val from tableB)
SELECT A.val FROM A, B
WHERE (A.val NOT IN (SELECT val FROM B)
       OR B.val NOT IN (SELECT val FROM A));
Mark Gerolimatos
  • 2,424
  • 1
  • 23
  • 33
  • Can't use [with](https://dev.mysql.com/doc/refman/8.0/en/with.html) with mysql in your example? – clinomaniac Apr 19 '18 at 23:00
  • 2
    If your query does not work, why do you want to "perform the following". What are you trying to do? Sample data and desired results would be really helpful. – Gordon Linoff Apr 19 '18 at 23:11
  • 2
    I'm having difficulty identifying what the question is. "DOES NOT WORK" is a rather vague description of the observed behavior. What *behavior* is being observed that leads us to the conclusion that something "DOES NOT WORK"? (My guess, and just a guess, is that a subquery is returning a NULL value, that is causing NOT IN to not evaluate to TRUE for any row. That behavior well documented, is not unique to MySQL, ... but is unexpected by the uninitiated. – spencer7593 Apr 19 '18 at 23:23
  • @spencer7593 The syntax in the first code block is illegal. The second code block is PostGRES. – Mark Gerolimatos Apr 19 '18 at 23:25
  • The syntax looks legal to me. True, the `FROM B` and `FROM A` subqueries don't refer to the aliases `A` and `B` assigned in the outer query. (I don't think that type of reference is supported in any database.) – spencer7593 Apr 19 '18 at 23:30
  • 1
    So your only description of what you want is a wrong query? Please read & act on [mcve]. Please edit your question to be clear. And alternatives to CTEs in MySQL (they will be in 8.0) is a faq. – philipxy Apr 19 '18 at 23:34

1 Answers1

2

UPDATE

MySQL 8.0 introduced support for Common Table Expressions (CTE) i.e. 'WITH' which earlier versions of MySQL do not support.

https://dev.mysql.com/doc/refman/8.0/en/with.html

EDIT

Yes, we can assign an alias to an inline view (or, derived table, in the MySQL venacular) and reference that alias elsewhere in the query. In this example, my_query is an alias we assign to the inline view.

 SELECT my_query.val 
   FROM ( SELECT val FROM foo )  my_query
  WHERE my_query.val 
  ORDER
     BY my_query.val

But we cannot reference that alias in the FROM clause of a subquery. For example, in this construct:

 SELECT my_query.val 
   FROM ( SELECT val FROM foo )  my_query
  WHERE 7 IN ( SELECT val FROM my_query )
  ORDER
     BY my_query.val

the syntax is actually valid. But the reference to my_query in the FROM clause of the IN (subquery) is not and can not be a reference to the table alias in the outer query. That's not allowed.

So we'd expect the query to probably throw an error about my_query reference in the subquery being unresolved identifier "unknown table", something of that ilk.

Note that We could create a table named my_query, e.g.

CREATE TABLE my_query (val INT PRIMARY KEY);
INSERT INTO my_query (val) VALUES (7);

And then re-run the query, and that reference my_query would now resolve to the table. (Not the alias assigned in the query.)

The Common Table Expression (CTE) introduced a significant step forward in resolving this type of issue, the ability to reference an inline view in multiple places where it isn't otherwise allowed.

If the question is, "How to emulate Common Table Expression (CTE) functionality in MySQL", a google search leads to this question on SO:

How do you use the "WITH" clause in MySQL?

original

Did you mean CTE (Common Table Expression i.e. the WITH clause) rather than CMT ?

If you meant CMT, I apologize, because it's not clear what that acronym represents, in the context of this question. Maybe it means Centrally Managed Table, but I don't see how that relates.

If you are asking if Common Table Expression (CTE) is supported in MySQL, the answer to that is no. Or, at least, not yet.

From the SQL examples posted, there doesn't appear to be any need for the CTEs or for inline views, or even subqueries. Perhaps the SQL has been over-simplified to the point that it's not clear what the query is supposed to achieve. (It's not supposed to get a symmetric difference, so we aren't going to suggest the obvious alternatives, how to do that in MySQL.

It is valid to use a subquery (a SELECT) with IN and NOT IN. These are both valid:

    foo IN ( subquery )

    foo NOT IN ( subquery )

One big "gotcha" with NOT IN (subquery) is when a NULL value is returned by the subquery. With a NULL value in the list, the NOT IN evaluates to NULL, which means it can't evaluate to TRUE for any row. As a demonstration, consider:

 SELECT 3 NOT IN (2,NULL,4)

Maybe that answers the question that's being asked.

We're kind of just guessing at what question is being asked. (Was there a question asked?)

What is meant by "DOES NOT WORK" ? The query is returning unexpected results? MySQL is returning an error? The query executes a long time and appears to "hang"? We're just guessing what DOES NOT WORK means.

Why are we using the old-school comma operator syntax for the join operation, rather than the (can we even still call it? newer) JOIN keyword?

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Yes, I mean CTE. CMT stands for something else in a totally different field, it occupies that part of my terminology cache :) – Mark Gerolimatos Apr 19 '18 at 23:26
  • Actually, the particulars of the query were just an example. A bunch of nasty left joins unioned with right joins, etc. get THAT job done. The point here is to illustrate that you cannot access a subquery as a first-level object as you can do with a *CTE* (not CMT) in PostGRES – Mark Gerolimatos Apr 19 '18 at 23:28
  • @spencer7583 because I am an old fart whose Database class with Michael Stonebreaker was one where the homework was really just QA'ing INGRES products :) I just prefer the older syntax, that's all. – Mark Gerolimatos Apr 19 '18 at 23:30
  • The FROM clause of a subquery can't reference an alias for an inline view (derived table) from the outer query. That's one of the most wonderous things about a CTE, the ability to assign an identifier to a inline view, and reference that multiple places within a SQL statement. That's not supported in MySQL (at least, not yet.) – spencer7593 Apr 19 '18 at 23:34
  • EXACTLY. I COMPLETELY fell in love with CTEs / WITH clauses in PostGRES. Unfortunately, the table I am currently working on is in a MySQL database :( – Mark Gerolimatos Apr 19 '18 at 23:36
  • I think maybe the question could be edited to clarify the question being asked. It's possible to assign an alias to inline view, and reference that alias elsewhere in the query. Is it possible/allowed/legal to reference that in the FROM clause of a subquery? The answer to that is *no*. It's *no* in MySQL just like it's *no* in any other database. (The CTE introduced a new workaround to this limitation. I've been working with SQL since IBM DB2 1.0 and VSAM, and with Oracle 7.x, ... and long ago I ditched the outdated comma syntax in favor of `JOIN .. ON`. – spencer7593 Apr 19 '18 at 23:43
  • I don't know, the old syntax I can just visualize in my head. I just think it looks nicer. Of course it makes it inconsistent with left joins and right joins Etc. Your mileage may vary :-) – Mark Gerolimatos Apr 19 '18 at 23:49