The Heap™ – Consultancy ©® - 2017-03-24 (page 1 of 2)

12:22 AM

@ErikE take for instance this...

SELECT * FROM ( VALUES (1),(2) ) AS t(x) CROSS JOIN ( VALUES (3),(t.x) ) AS g(y);

Does that work in MS SQL without CROSS APPLY?

It shouldn't but I have no idea whether or not it does.

Also, yes in PostgreSQL we use CROSS APPLY TO run SRF that's reference an external part of the query, this can force the evaluation. For instance, this SELECT x FROM generate_series(1,3) AS t(x) CROSS JOIN LATERAL generate_series(1,t.x); will generate..

 x
---
 1
 2
 2
 3
 3
 3
(6 rows)

McNets

@EvanCarroll I'm sorry, I have not seen your comments. Let me take a look.

ErikE

12:39 AM

@EvanCarroll Of course that doesn't work without CROSS APPLY, but that's not what I said works. I said in the ON clause you can have an outer reference or refer to any table that has already been introduced.

Evan Carroll

What would it even look like if you couldn't reference other tables previously introduced in a WHERE clause or an ON clause?

ErikE

@EvanCarroll It wouldn't make any sense! But that's why it was a learning process for me. Because of the practice of using CROSS APPLY with its cool outer references, I started thinking differently. That different thinking dawned on me one day as applicable to most of the other parts of the query. I started doing cool stuff in ON, WHERE, and SELECT clauses I had never done before.

Stuff with outer references.

Evan Carroll

Yea, I think the best way to think about it is to ignore that though and just ask, does this query need to be evaluated once per row, or once per query.

That's how I use LATERAL in my head.

ErikE

@EvanCarroll No, I'm not referring to anything that mundane. I'm talking about radically different queries I'd never thought of before.

@EvanCarroll I guess I mean, that yes, I was already in once-per-row land. I wasn't even thinking of once-per-query land. But sometimes you can do the same thing in two different ways.

SELECT
FROM
   dbo.SomeTableWithDuplicates t
   INNER JOIN (
       SELECT d.OtherId
       FROM dbo.SomeTableWithDuplicates d
       GROUP BY d.OtherId
       HAVING Count(*) >= 2
   ) d ON t.OtherId = d.OtherId;

This query can easily be rewritten as a row-by-row operation.

So your dividing question, while useful in many contexts, isn't always all that helpful (it often comes down to how much of the table you expect to query).

SELECT
FROM
   dbo.SomeTableWithDuplicates t
WHERE
   EXISTS (
      SELECT *
      FROM dbo.SomeTableWithDuplicates d
      WHERE
         t.Id <> d.Id
         AND t.OtherId = d.OtherId
   );

There, it's rewritten to do the same job (and will work much faster if you have a condition on the t table that selects only a subset of rows).

And the mind-blowing version:

SELECT
FROM
  dbo.SomeTableWithDuplicates t
WHERE
  EXISTS (
      SELECT *
      FROM dbo.SomeTableWithDuplicates d
      WHERE t.OtherId = d.OtherId
      HAVING Count(*) >= 2
  );

Not as efficient, but in some cases this style of query is useful (aggregate within EXISTS clause).

Evan Carroll

Don't you need a group by in that?

ErikE

12:50 AM

@EvanCarroll Nope. Try it. :) Mind-blowing, I said.

Evan Carroll

I can't try it, I don't have sample data. I don't think PostgreSQL you can run aggregates against un-grouped rows unless they just consist of a primary key.

I think what's it's doing there is an implicit GROUP BY with all of the columns

Which is something like what MySQL does too

ErikE

@EvanCarroll Incorrect

Evan Carroll

SELECT * FROM ( VALUES (1,2) )  AS t(x,y) HAVING count(*) > 1;

That doesn't work in Microsoft SQL.

I'm not sure how your exist statement works with HAVING(*) if the above does not work.

ErikE

@EvanCarroll Stick that in an EXISTS clause

Evan Carroll

SELECT 1 WHERE EXISTS ( SELECT * FROM ( VALUES (1,2) ) AS t(x,y) HAVING count(*) > 1 );
ERROR: column "t.x" must appear in the GROUP BY clause or be used in an aggregate function

Doesn't work in PostgreSQL

ErikE

12:54 AM

need an outer reference to implicitly group by

@EvanCarroll No, no outer reference needed, works in SQL Server. The * is irrelevant.

Evan Carroll

SELECT * FROM ( VALUES (1) ) AS g(x) WHERE EXISTS ( SELECT * FROM ( VALUES (1,2) ) AS t(x,y) WHERE g.x = t.x HAVING count(*) > 1 );
ERROR: column "t.x" must appear in the GROUP BY clause or be used in an aggregate function

ErikE

@EvanCarroll Must not work in PostGreSQL (it pays attention to the * when it should ignore it)

Evan Carroll

I don't agree that. I think this is just non-standard syntax that Microsoft implemented. But, I could be wrong.

ErikE

@EvanCarroll I don't agree. It makes perfect sense to me.

McNets

@EvanCarroll I'm sorry to disturb you but FILTER requires a window definition and I'm not able to figure it out now. 1:56 AM I'm going to sleep. I'll try again tomorrow. Nite all

Evan Carroll

12:57 AM

I'll look at it in a sec @McNets, good night.

@ErikE it's likely an implementation detail. Microsoft is grouping under the hood and rather than denying you the ability to use that non-standard syntax, they expose it misleading others into thinking it works.

McNets

rextester.com/WUW44121

Evan Carroll

@ErikE I don't think (a) that EXISTS requires a GROUP BY implicit or otherwise, or that (b) the syntax is supported by the spec.

That said, I could be totally and utterly wrong.

Let me turn it into a question. ;)

@ErikE why is this returning x and not 1? dbfiddle.uk/…

ErikE

@EvanCarroll Dangit I was trying dbfiddle.com and dbfiddle.net and just could NOT find that thing! Finally.

@EvanCarroll It doesn't return anything. There's a single column, x, with no rows.

Evan Carroll

wow. that's a horrible display.

ErikE

I was on the phone so couldn't explain much. I'll explain now.

Evan Carroll

1:05 AM

@JackDouglas feature request! ^^ row counts

ErikE

@EvanCarroll Be sure to lodge your complaints with the author!

@EvanCarroll The trick with EXISTS is that the SELECT list is irrelevant. DBMSes are actually supposed to ignore (or at least, can ignore) the SELECT list inside an EXISTS clause.

Additionally, a correlated subquery is implicitly grouped by the correlation keys

Evan Carroll

PostgreSQL is ignoring the select list.

ErikE

@EvanCarroll But it may be parsing it and then tossing it later.

Evan Carroll

no, it's not

ErikE

If you do CROSS APPLY (SELECT Max(Val) FROM SomeTable t WHERE a.Id = t.Id) (notice the outer reference to table t) you do NOT need a GROUP BY because it is implicitly grouping by the correlation key, a.Id.

Evan Carroll

1:07 AM

It's just invalid syntax because you can't do HAVING without GROUP BY

you do not need a group by because you do not have a HAVING

ErikE

What I noticed in my own queries is that this implicit grouping lets you dump the GROUP BY clause, if the GROUP BY expression exactly matches the correlation key.

Evan Carroll

in your exists query, you have a HAVING. So you need a group by.

ErikE

Well, whether it's peculiar to SQL Server or not, you don't need a GROUP BY because a correlated subquery is implicitly grouped :)

Evan Carroll

That may be true, but that's a violation of standard. MySQL does that kind of stuff too.

ErikE

Let me cook up an example in dbfiddle

@EvanCarroll Show me the standard on EXISTS clauses that says this, and I'll believe you. :)

@EvanCarroll You can put a GROUP BY if you want to, but you don't need to.

Evan Carroll

1:09 AM

See, not ever EXISTS should result in a grouping to begin with, what if there is enough rows to pay for the aggregation and the planner wants to take a different route? It's exposing an implementation detail which could be optimized away.

Transcript for

The Heap™ – Consultancy ©®