Press "Enter" to skip to content

Category: Syntax

T-SQL Order of Execution and Aliases

Published 2022-05-19 by Kevin Feasel

Joe Billingham explains why you can’t do that thing you want to do:

So, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]‘.

The column you’ve used in your WHERE clause cannot be identified by its alias. You’ve defined it at the top of the query and used it fine previously as your ORDER BY condition, so why can’t the engine recognise it?

Read on for the answer. This is why some people I know have wanted a SQL-like language which runs in order of execution, so a query would start with the FROM clause rather than the SELECT clause. Languages like KQL do work that day, so there are examples in the wild.

Comments closed

Stored Functions in MySQL

Published 2022-05-10 by Kevin Feasel

Robert Sheldon continues a series on MySQL:

In the previous three articles in this series, I focused on creating basic database objects that you can use to get started with MySQL. You learned how to build an initial database and then add tables, views, and stored procedures. In this article, I cover one more important type of object, the stored function, a routine that is stored in a database and can be invoked on-demand, similar to a user-defined scalar function in SQL Server or other database systems.

I’m not familiar enough with stored functions to know if they have the same performance limitations as what we have in SQL Server (specifically around needing to run everything in the function once for each row) but based on a comment at the end of Robert’s post, it does seem that way.

Comments closed

Counting in KQL

Published 2022-05-10 by Kevin Feasel

Robert Cain continues a series on KQL operations:

The previous post in the series covered the take operator. In that post I mentioned that take was one of the simplest operators in KQL. But it is not the simplest, that honor goes to the count operator.

The count operator does nothing more than takes the piped in dataset and returns the number of rows in it. We’ll see more in a moment.

Click through to see more.

Comments closed

Fun with Natural Full Join

Published 2022-05-05 by Kevin Feasel

Lukas Eder shows off natural joins:

At first I though of the UNION CORRESPONDING syntax, which doesn’t really exist in most SQL dialects, even if it’s a standard feature. But then, I remembered that this is again a perfect use case for NATURAL FULL JOIN, this time slightly differently from the above example where two tables are compared for contents. This time, we want to make sure the two joined tables never have matching rows, in order to get the UNION like behaviour.

I wasn’t aware of the notion of natural joins because they’re not available in SQL Server. They are available in Oracle, Postgres, and MySQL. Fun as Lukas’s blog post is, I could see natural joins going wrong in so many ways.

Comments closed

The take Operator in KQL

Published 2022-05-03 by Kevin Feasel

Robert Cain continues a series on KQL:

In this example we took the Perf table, and piped the dataset it generated into the take operator. We indicated we wanted to get 10 rows, which it did as you can see.

It is important to understand that take grabs these rows at random. Further, there is no consistency between each execution of take. You are likely to get a different set of rows with each execution. Let me run the exact same query again, so you can see the different data being returned.

Take if you want a slice, if you want a piece, if it feels alright.

Comments closed

Empty Catch Blocks in T-SQL

Published 2022-05-03 by Kevin Feasel

Erik Darling looks disapprovingly:

So if you execute the above proc, all it returns is an empty result with no error message.

Kinda weird.

Like not having finger or toenails.

I’m dating myself here but I remember a comment in a TheDailyWTF thread in which somebody puts an ASCII art Pikachu in every empty catch block because hey, you gotta catch ’em all.

Comments closed

Multiple Aggregations with CASE

Published 2022-05-03 by Kevin Feasel

Chad Callihan shows off a good use of aggregate functions and the CASE statement:

Have you have been tasked with pulling multiple counts from the same table? Maybe you need to find how many records have a value for a column and how many are NULL. Or maybe you need to see how many records are true and how many are false.

It’s simple enough to run a query to count one set of criteria, run another query for the second set of criteria, and combine them when sending your results. Did you know you can get multiple counts with one query?

Here’s an example of how using COUNT and CASE can speed up your day.

Click through for an example. I mildly disagree with Chad’s conclusion that this is something you’ll rarely do—the more you work with reporting and analytical queries, the more you’ll appreciate this.

Comments closed

Splitting Strings with Quoted Names

Published 2022-04-22 by Kevin Feasel

Daniel Hutmacher mixes separators with regular characters:

Suppose you have a delimited string input that you want to split into its parts. That’s what STRING_SPLIT() does:

DECLARE @source nvarchar(max)='Canada, Cape Verde, '+    'Central African Republic, Chad, Chile, China, Colombia, Comoros';

SELECT TRIM([value]) AS[Country]
FROM STRING_SPLIT(@source, ',');

Simple enough. But delimited lists are tricky, because the delimiter could exist in the name itself. Look for yourself what happens when we add the two Congos to the list:

Daniel has a clever solution to the problem.

Comments closed

Calculating Running Totals with Window Functions

Published 2022-04-22 by Kevin Feasel

Steve Jones shows off a good use case for window functions:

Recently I was looking at some data and wanted to analyze it by month. I have a goal that is set for each day and then an actual value. I wanted to know how I was tracking against the goal, as a running total. If my goal is 10 a day, then I ought to actually get to 10 the first day, 20 for the second day (10 + 10), etc.

Read on to see how Steve solved the problem.

Comments closed

Animated SQL: Visualizing Query Operations

Published 2022-04-19 by Kevin Feasel

Steve Jones looks at an interesting site:

While I think SQL is interesting, I know some people struggle with the way the language work. Someone at work posted a link to this site: https://animatesql.com/

I think the idea is this site helps you visualize how a SQL query works. It’s not free form, and I can’t just write any SQL, but you choose a keyword and then a sample query is shown. If you press Visualize, it walks through how this query is processed.

Click through to see how it works and Steve’s thoughts. It looks like they’re using either MySQL or Postgres in the background; it’s hard to tell because both support all of the site functionality including LIMIT/OFFSET (versus TOP and OFFSET/FETCH). Sadly, it’s pretty limited in terms of the queries supported—for example, I tried adding in a quick ROW_NUMBER() window function and that did not go over well. Still, I like this a lot as a teaching tool, especially for people brand new to SQL and haven’t sorted out how to think in sets.

Comments closed