Tech & more blog

At my current job, we use Snowflake’s data platform. I’ve used it before, but not that much. A couple of weeks ago, I had to delete some data from a table, but to do it, I needed to make sure that the records also existed in another table. In other words, I needed to execute a DELETE based on a JOIN.

The database engine I have worked with the most is MS SQL Server, so I started typing the code as I would in it. Now, before we go into my preferred syntax, let’s see the 3 ways you could do a DELETE based on JOIN in SQL Server:

1. Delete using a subquery (ISO-compatible subquery solution).

DELETE FROM dbo.User
WHERE ID IN
    (SELECT ID
     FROM dbo.InactiveUser
     WHERE DisabledDate = CAST('2023-01-27' AS DATE));

2. Delete using the Transact-SQL FROM extension to join two tables (Option 1).

DELETE FROM dbo.User
FROM dbo.User AS U
INNER JOIN dbo.InactiveUser AS IU
ON U.ID = IU.ID
WHERE IU.DisabledDate = CAST('2023-01-27' AS DATE);

3. Delete using the Transact-SQL FROM extension to join two tables (Option 2).

DELETE U
FROM dbo.User AS U
INNER JOIN dbo.InactiveUser AS IU
ON U.ID = IU.ID
WHERE IU.DisabledDate = CAST('2023-01-27' AS DATE);

My preferred syntax or way of doing a DELETE based on a JOIN is the last one. I don’t need to repeat the name of the driving table, and I don’t know if the following could be considered a trick, but I can quickly see the records I’m about to delete by just commenting out the first line and adding a new line below with a SELECT *. It feels quick for me.

--DELETE U
SELECT *
FROM dbo.User AS U
INNER JOIN dbo.InactiveUser AS IU
ON U.ID = IU.ID
WHERE IU.DisabledDate = CAST('2023-01-27' AS DATE);

Also, from a readability standpoint, I feel the code looks simpler compared to the first option as the second I see a subquery, my brain perceives this as a complex operation.

Having explained the syntax of my preference, and therefore the one I tried to use in Snowflake, little did I know that it was going to fail with the following error:

Why? you would ask. Well, my preferred syntax is part of Microsoft’s extension of SQL, Transact-SQL, specifically, an extension of the FROM clause when the DELETE statement is being used.

On the other hand, Snowflake’s data platform supports standard SQL, including a subset of ANSI SQL:1999 and the SQL:2003 analytic extensions. In other words, Snowflake simply doesn’t support the FROM extension implemented in Transact-SQL.

Well, if I cannot execute a DELETE in the way I’m used to, then how? The answer is the USING clause. The USING clause allows the use of a similar syntax. Here is the syntax and the previously used example implemented in Snowflake:

DELETE FROM <table_name>
            [ USING <additional_table_or_query> [, <additional_table_or_query> ] ]
            [ WHERE <condition> ]

DELETE FROM dbo.User AS U
USING dbo.InactiveUser AS IU
WHERE U.ID = IU.ID
AND UI.DisabledDate = CAST('2023-01-27' AS DATE);

Now we don’t get errors:

Summary

In this post, we learned about the existence of the USING clause in Snowflake and how it is used to execute a DELETE statement based on a JOIN. We also saw some technical details about the SQL versions Snowflake supports.

I hope this was helpful to you 🙂