When does SELECT query starts with a 'With'

Question

I was reading this question How to determine type of the sql query for jdbcTemplate in JAVA?. The correct answer says, a SELECT query can either start with a SELECT or a WITH.

Just for reference, the answer said:

// SELECT subqueries are irrelevant for the final result. So the command, the first verb is indicative of the result (int updateCount vs. ResultSet).

boolean isSqlSelect = sql.toUpperCase().startsWith("SELECT")
                   || sql.toUpperCase().startsWith("WITH");

So, now lets say I have a query

SELECT id, name, email FROM users;

Questions

How can I rewrite that query starting with a WITH?
And although the question was for JDBC, is it common for all SQL and ODBC/JDBC?

Gordon Linoff · Accepted Answer · 2020-07-28T19:10:05.773

6

WITH is the syntax for common table expressions (CTEs). This is standard SQL syntax supported basically by all reasonable databases (MySQL was the last to add such support).

CTEs are used to define subqueries that can be referenced multiple times in the query.

Note that CTEs can be used with UPDATE and DELETE statements as well as SELECT, so they do not necessarily indicate a SELECT.

edited Jul 28 '20 at 19:10

answered Jul 28 '20 at 19:01

Gordon Linoff

1,242,037
58
646
786

So is it common to all DBs supporting sql? – Chilarai Jul 28 '20 at 19:06
2

@Chilarai I researched this and found that except for Informix, all major SQL implementations now support `WITH` syntax for CTE's (MySQL 8.0 was the last one to adopt it). https://stackoverflow.com/a/325243/20860 – Bill Karwin Jul 28 '20 at 19:09
1

@Chilarai . . . Pretty much anything I would call a database. – Gordon Linoff Jul 28 '20 at 19:10
Correction: Informix 14.10 added support for `WITH` in version 14.10, in 2019. I've updated the answer I linked to above. – Bill Karwin Jul 28 '20 at 19:13
@BillKarwin . . . That leaves out MS Access, which I am happy to leave out. – Gordon Linoff Jul 28 '20 at 19:13
1

Oy! MS Access lacks _tons_ of standard SQL features. Don't even bother to list what's missing. – Bill Karwin Jul 28 '20 at 19:14

Radagast · Answer 2 · 2020-07-28T19:18:56.757

4

Since you also asked how you could rewrite your query using CTE...

with cte as

(select id, name, email 
 from users)

select * from cte;

I find it cleaner to define column names early on in the with clause so I save some space for my select

with cte (id, name, email) as

(select *
 from users)

select * from cte;

edited Jul 28 '20 at 19:18

answered Jul 28 '20 at 19:14

Radagast

5,102
3
12
27

When does SELECT query starts with a 'With'

2 Answers2