mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-12 15:33:44 +00:00
176 lines
4.3 KiB
Plaintext
176 lines
4.3 KiB
Plaintext
|
|
CREATE TABLE c1(a);
|
|
INSERT INTO c1 VALUES(1), (2), (4), (3);
|
|
|
|
|
|
SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1
|
|
|
|
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY);
|
|
INSERT INTO t1 VALUES(1);
|
|
SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3);
|
|
|
|
|
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t2(a INTEGER PRIMARY KEY,b);
|
|
INSERT INTO t2 VALUES(1,11);
|
|
INSERT INTO t2 VALUES(2,22);
|
|
INSERT INTO t2 VALUES(3,33);
|
|
SELECT b, a IN (3,4,5) FROM t2 ORDER BY b;
|
|
|
|
|
|
DROP TABLE IF EXISTS t3;
|
|
CREATE TABLE t3(x INTEGER PRIMARY KEY);
|
|
INSERT INTO t3 VALUES(8);
|
|
SELECT CASE WHEN x NOT IN (5,6,7) THEN 'yes' ELSE 'no' END FROM t3;
|
|
SELECT CASE WHEN x NOT IN (NULL,6,7) THEN 'yes' ELSE 'no' END FROM t3;
|
|
|
|
|
|
SELECT CASE WHEN x NOT IN (5,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
|
|
SELECT CASE WHEN x NOT IN (NULL,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
|
|
|
|
|
|
DROP TABLE IF EXISTS t4;
|
|
CREATE TABLE t4(a INTEGER PRIMARY KEY, b INT);
|
|
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)
|
|
INSERT INTO t4(a,b) SELECT x, x+100 FROM c;
|
|
SELECT b FROM t4 WHERE a IN (3,null,8) ORDER BY +b;
|
|
|
|
|
|
SELECT b FROM t4 WHERE a NOT IN (3,null,8);
|
|
|
|
|
|
DROP TABLE IF EXISTS t5;
|
|
DROP TABLE IF EXISTS t6;
|
|
CREATE TABLE t5(id INTEGER PRIMARY KEY, name TEXT);
|
|
CREATE TABLE t6(id INTEGER PRIMARY KEY, name TEXT, t5_id INT);
|
|
INSERT INTO t5 VALUES(1,'Alice'),(2,'Emma');
|
|
INSERT INTO t6 VALUES(1,'Bob',1),(2,'Cindy',1),(3,'Dave',2);
|
|
SELECT a.*
|
|
FROM t5 AS 'a' JOIN t5 AS 'b' ON b.id=a.id
|
|
WHERE b.id IN (
|
|
SELECT t6.t5_id
|
|
FROM t6
|
|
WHERE name='Bob'
|
|
AND t6.t5_id IS NOT NULL
|
|
AND t6.id IN (
|
|
SELECT id
|
|
FROM (SELECT t6.id, count(*) AS x
|
|
FROM t6
|
|
WHERE name='Bob'
|
|
) AS 't'
|
|
WHERE x=1
|
|
)
|
|
AND t6.id IN (1,id)
|
|
);
|
|
|
|
|
|
CREATE TABLE x1(a, b);
|
|
INSERT INTO x1(a) VALUES(1), (2), (3), (4), (5), (6);
|
|
CREATE INDEX x1i ON x1(a, b);
|
|
|
|
|
|
SELECT * FROM x1
|
|
WHERE a IN (SELECT a FROM x1 WHERE (a%2)==0)
|
|
ORDER BY a DESC, b;
|
|
|
|
|
|
SELECT * FROM x1
|
|
WHERE a IN (SELECT a FROM x1 WHERE (a%7)==0)
|
|
ORDER BY a DESC, b;
|
|
|
|
|
|
SELECT 1 IN ('1');
|
|
|
|
|
|
SELECT 1 IN ('1' COLLATE nocase);
|
|
|
|
|
|
SELECT 1 IN (CAST('1' AS text));
|
|
|
|
|
|
SELECT 1 IN (CAST('1' AS text) COLLATE nocase);
|
|
|
|
|
|
DROP TABLE IF EXISTS t0;
|
|
CREATE TABLE t0(c0 INT UNIQUE);
|
|
INSERT INTO t0(c0) VALUES (1);
|
|
SELECT * FROM t0 WHERE '1' IN (t0.c0);
|
|
|
|
|
|
DROP TABLE IF EXISTS t0;
|
|
CREATE TABLE t0(c0 REAL UNIQUE);
|
|
INSERT INTO t0(c0) VALUES(2.0625E00);
|
|
SELECT 1 FROM t0 WHERE c0 IN ('2.0625');
|
|
|
|
|
|
SELECT c0 IN ('2.0625') FROM t0;
|
|
|
|
|
|
SELECT c0 = ('2.0625') FROM t0;
|
|
|
|
|
|
SELECT c0 = ('0.20625e+01') FROM t0;
|
|
|
|
|
|
SELECT c0 IN ('2.0625',2,3) FROM t0;
|
|
|
|
|
|
DROP TABLE t0;
|
|
CREATE TABLE t0(c0 TEXT, c1 REAL, c2, PRIMARY KEY(c2, c0, c1));
|
|
CREATE INDEX i0 ON t0(c1 IN (c0));
|
|
INSERT INTO t0(c0, c2) VALUES (0, NULL) ON CONFLICT(c2, c1, c0) DO NOTHING;
|
|
PRAGMA integrity_check;
|
|
|
|
|
|
SELECT (1 IN (2 IS TRUE));
|
|
|
|
|
|
CREATE TABLE t0(c0);
|
|
SELECT COUNT(*) FROM t0 ORDER BY (t0.c0 IN ());
|
|
|
|
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(x INT PRIMARY KEY, y INT);
|
|
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<8)
|
|
INSERT INTO t1(x,y) SELECT x, x*100 FROM c;
|
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t2(a INT);
|
|
INSERT INTO t2 VALUES(2),(4),(6);
|
|
SELECT * FROM t1 WHERE x IN (SELECT a FROM t2);
|
|
|
|
|
|
SELECT * FROM t1 WHERE x IN ((SELECT a FROM t2));
|
|
|
|
|
|
SELECT * FROM t1 WHERE x IN (((SELECT a FROM t2)));
|
|
|
|
|
|
SELECT * FROM t1 WHERE x IN ((((((SELECT a FROM t2))))));
|
|
|
|
|
|
DROP TABLE IF EXISTS t4;
|
|
CREATE TABLE t4(a TEXT, b INT);
|
|
INSERT INTO t4(a,b) VALUES('abc',0),('ABC',1),('def',2);
|
|
CREATE INDEX t4x ON t4(a, +a COLLATE NOCASE);
|
|
SELECT a0.a, group_concat(a1.a) AS b
|
|
FROM t4 AS a0 JOIN t4 AS a1
|
|
GROUP BY a0.a
|
|
HAVING (SELECT sum( (a1.a == +a0.a COLLATE NOCASE) IN (SELECT b FROM t4)));
|
|
|
|
|
|
SELECT a0.a, group_concat(a1.a) AS b
|
|
FROM t4 AS a0 JOIN t4 AS a1
|
|
GROUP BY a0.a
|
|
HAVING (SELECT sum( (a1.a GLOB +a0.a COLLATE NOCASE) IN (SELECT b FROM t4)));
|
|
|
|
|
|
CREATE VIEW t5 AS
|
|
SELECT 1 AS b
|
|
WHERE (SELECT count(0=NOT+a COLLATE NOCASE IN (SELECT 0))
|
|
FROM t4
|
|
GROUP BY a);
|
|
SELECT * FROM t5;
|
|
|