Combination of 4 numbers in Mysql

Question

SGBD: Mysql

Problem: List all possible combinations between the numbers "1,2,3,4", so that they do not recur between them.

Example:

1
2
3
4
12
13
14
21
23
24
31
32
34
41
42
43...

Answer 1

Kind of weird to need to do this in SQL, but having a table with the digits, just do a CROSS JOIN with herself:

SELECT CONCAT(d1.digito, d2.digito)
FROM digitos d1
CROSS JOIN digitos d2
WHERE d1.digito <> d2.digito

^Demo

This works for two-digit combinations. With one to four digits it is more complicated, and SQL is not the appropriate tool to solve this... Even so, here it goes, considering that the digit table contains NULL, 1, 2, 3 and 4:

SELECT CONCAT(d1.digito, IFNULL(d2.digito,''), IFNULL(d3.digito,''), IFNULL(d4.digito,''))
FROM digitos d1
LEFT JOIN digitos d2
ON d1.digito <> d2.digito
OR d2.digito IS NULL

LEFT JOIN digitos d3
ON (d2.digito <> d3.digito AND d1.digito <> d3.digito)
OR d3.digito IS NULL

LEFT JOIN digitos d4
ON (d1.digito <> d4.digito AND d2.digito <> d4.digito AND d3.digito <> d4.digito)
OR d4.digito IS NULL

WHERE d1.digito IS NOT NULL

ORDER BY 0+CONCAT(d1.digito, IFNULL(d2.digito,''), IFNULL(d3.digito,''), IFNULL(d4.digito,''))

^Demo