left sql join

leftJoin.sql

-- Sample left join 
-- see for more detail, https://www.tutorialspoint.com/sql/sql-left-joins.htm
-- to execute: 
-- sqlite3 ./db < leftjoin.sql
--
drop table if exists U;
drop table if exists S;

create table if not exists U (address text, txid text, txix int , unique (txid, txix) );
create table if not exists S (txid text, txix int, unique (txid, txix));

insert into U (address, txid, txix) values ("a", "id-0", 0);
insert into U (address, txid, txix) values ("b", "id-0", 1);
insert into U (address, txid, txix) values ("c", "id-0", 2);
insert into U (address, txid, txix) values ("a", "id-0", 3);
insert into U (address, txid, txix) values ("a", "id-0", 4);
insert into S (txid, txix) values ("id-0", 4);
insert into S (txid, txix) values ("id-0", 5);
insert into S (txid, txix) values ("id-0", 6);
--
-- represents U - S, all elements in U that are not in S
--
select u.address, u.txid, u.txix
       from U u LEFT JOIN S s
       on u.txid = s.txid  AND u.txix = s.txix
       where
       u.address = 'a' AND
       S.txid is null AND S.txix is null;