|
HSQLDB example source code file (TestSelfInPredicateReferencing.txt)
The HSQLDB TestSelfInPredicateReferencing.txt source codeDROP TABLE dups IF EXISTS; CREATE TABLE dups(pk INTEGER NOT NULL PRIMARY KEY,val VARCHAR(10) NOT NULL); INSERT INTO dups VALUES (1, 'first'); INSERT INTO dups VALUES (2, 'second'); INSERT INTO dups VALUES (3, 'third'); INSERT INTO dups VALUES (4, 'first'); INSERT INTO dups VALUES (5, 'first'); INSERT INTO dups VALUES (6, 'second'); SELECT sa.pk FROM dups sa, dups sb WHERE sa.val=sb.val AND sa.pk!=sb.pk; SELECT a.pk, a.val FROM dups a WHERE a.pk in (1, 2, 4, 5, 6); SELECT a.pk, a.val FROM dups a WHERE a.pk in (SELECT sa.pk FROM dups sa, dups sb WHERE sa.val=sb.val AND sa.pk!=sb.pk); -- ALL /*r 1,first 4,first 5,first */SELECT a.pk, a.val FROM dups a WHERE a.val = ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'first') ORDER BY a.pk; /*r 1,first 2,second 3,third 4,first 5,first 6,second */SELECT a.pk, a.val FROM dups a WHERE a.val >= ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'first') ORDER BY a.pk; /*r 2,second 3,third 6,second */SELECT a.pk, a.val FROM dups a WHERE a.val > ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'first') ORDER BY a.pk; /*r 1,first 2,second 4,first 5,first 6,second */SELECT a.pk, a.val FROM dups a WHERE a.val <= ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'second') ORDER BY a.pk; /*r 1,first 2,second 4,first 5,first 6,second */SELECT a.pk, a.val FROM dups a WHERE a.val < ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'third') ORDER BY a.pk; /*r 1,first 4,first 5,first */SELECT a.pk, a.val FROM dups a WHERE a.val <= ALL(SELECT sa.val FROM dups sa) ORDER BY a.pk; -- ANY /*r 1,first 2,second 4,first 5,first 6,second */SELECT a.pk, a.val FROM dups a WHERE a.val < ANY(SELECT sa.val FROM dups sa) ORDER BY a.pk; /*r 2,second 3,third 6,second */SELECT a.pk, a.val FROM dups a WHERE a.val > ANY(SELECT sa.val FROM dups sa) ORDER BY a.pk; /*r 1,first 2,second 3,third 4,first 5,first 6,second */SELECT a.pk, a.val FROM dups a WHERE a.val <= ANY(SELECT sa.val FROM dups sa) ORDER BY a.pk; /*c0*/SELECT a.pk, a.val FROM dups a WHERE a.val <= ANY(SELECT NULL FROM dups sa) ORDER BY a.pk; /*c0*/SELECT a.pk, a.val FROM dups a WHERE a.val <= ANY(SELECT NULL FROM dups sa WHERE sa.val ='fourth') ORDER BY a.pk; -- non-correlated single value /*r 1,first 4,first 5,first */SELECT a.pk, a.val FROM dups a WHERE a.val = (SELECT sa.val FROM dups sa WHERE sa.pk = 4) ORDER BY a.pk; -- bug item #1100384 drop table a if exists; drop table b if exists; drop table m if exists; create table a(a_id integer); create table b(b_id integer); create table m(m_a_id integer, m_b_id integer); insert into a(a_id) values(1); insert into b(b_id) values(10); insert into m(m_a_id, m_b_id) values(1, 5); insert into m(m_a_id, m_b_id) values(1, 10); insert into m(m_a_id, m_b_id) values(1, 20); /*r1,10*/select a.a_id, b.b_id from a, b where a.a_id in (select m.m_a_id from m where b.b_id = m.m_b_id); /*R1,10*/select a.a_id, b.b_id from a join b on a.a_id in (select m.m_a_id from m where b.b_id = m.m_b_id); -- DROP TABLE T IF EXISTS; CREATE TABLE T(C VARCHAR_IGNORECASE(10)); INSERT INTO T VALUES ('felix'); /*r felix */SELECT * FROM T WHERE C IN ('Felix', 'Feline'); /*c0*/SELECT * FROM T WHERE C IN ('Pink', 'Feline'); DROP TABLE T; CREATE TABLE T(C CHAR(10)); INSERT INTO T VALUES ('felix'); /*c1*/SELECT * FROM T WHERE C IN ('felix', 'pink'); DROP TABLE T; Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSelfInPredicateReferencing.txt source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.