Even numbers: SQL

Robin Michael -

Problem: Print out the even numbers between 1 and 10 inclusive

Normally you would a simple for/while loop and check for modulus, division but let's do it with SQL and recursive Common Table Expressions CTE

MySql

WITH RECURSIVE seq AS (SELECT 1 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 11)
SELECT * FROM seq WHERE seq.value % 2 = 0;

MS SQL

WITH [seq] AS (SELECT 1 as value UNION ALL SELECT value + 1 FROM [seq] WHERE Value < 11)
SELECT * FROM [seq] [s] WHERE [s].[value] % 2 = 0 OPTION (MAXRECURSION 100);

Sqlite

WITH t(val) AS ( SELECT 1 val UNION ALL SELECT val+1 FROM t WHERE val < 11)
SELECT * FROM t WHERE t.val % 2 = 0;

Postgres

WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 11)
SELECT * FROM t WHERE n % 2 = 0;

And for fun use the

  • seq command seq 2 2 11
  • powershell command 2..11 | Where-Object { $_ % 2 -eq 0 }
  • bash expansion echo {2..10..2}