sub-query substitution in sqlite query

Question

I am new to SQLite and SQL. I was playing with a few nested queries and found that I was repeatedly using some subqueries. I wanted to know if there is any way to simplify the queries for better readability. Consider the following query.

SELECT *
FROM
    table1
INNER JOIN
(
    SELECT *
    FROM
        table2
    WHERE
        col1=1234
) t
ON table1.col3=t.col5

I wanted to simplify this by something equivalent to the following (in C macro syntax):

#define MYMACRO(X) SELECT * FROM table2 WHERE col1=X
SELECT * FROM table1 INNER JOIN MYMACRO(1234) t ON table1.col3=t.col5

Is this possible ?

Note: I know I can simplify the first query so that it would not be nested. I am just using it for explaining my question.

What you are asking for is a table-valued function. These are not easily defined in SQLite. — Gordon Linoff, Aug 29 '18 at 11:11

score 0 · Answer 1 · answered Aug 29 '18 at 10:14

0

WITH is the keyword you're looking for.

With MyMacro AS SELECT * FROM table2 WHERE col1=@parameter

SELECT * FROM table1 INNER JOIN MyMacro ON table1.col3=MyMacro.col5

answered Aug 29 '18 at 10:14

OwlsSleeping

1,487
2
11
19

Sorry, no variables in SQLite. This goes over alternatives https://stackoverflow.com/questions/7739444/declare-variable-in-sqlite-and-use-it – OwlsSleeping Aug 29 '18 at 10:16

sub-query substitution in sqlite query

1 Answers1