USING SELECT 'X' in query/sub-queries.
--------------------------------------------------------
-- DDL for Table TAB1
--------------------------------------------------------
CREATE TABLE "ATEST"."TAB1"
( "ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) ;
Insert into ATEST.TAB1 (ID,NAME) values (1,'AAA');
Insert into ATEST.TAB1 (ID,NAME) values (2,'BBB');
Insert into ATEST.TAB1 (ID,NAME) values (3,'EEE');
Insert into ATEST.TAB1 (ID,NAME) values (4,'FFF');
--------------------------------------------------------
-- DDL for Table TAB2
--------------------------------------------------------
CREATE TABLE "ATEST"."TAB2"
( "ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) ;
Insert into ATEST.TAB2 (ID,NAME) values (1,'CCC');
Insert into ATEST.TAB2 (ID,NAME) values (2,'DDD');
Get records that exits in TAB1 and not in TAB2 using select 'X' :
select * from TAB1 f where not exists (select 'X' from TAB2 where id=f.id);
ID NAME
-- ----
4 FFF
3 EEE
IN the above query we get output of all the records from TAB1 that doesnt match with TAB2 ID's.
Hence we do not get the records with ID's 1 & 2 as they only exits in TAB1.
This is just like using "select * from TAB1 f where not exists (select ID from TAB2 where id=f.id);"
Get records that exits in TAB1 and in TAB2 using select 'X' :
select * from TAB1 f where exists (select 'X' from TAB2 where id=f.id);
ID NAME
-- ----
1 AAA
2 BBB
IN the above query we get output of all the records from TAB1 that exist with same ID in TAB2 .
Hence we get only records with ID 1 & 2 as they exists in both TABLES.
This is just like using "select * from TAB1 f where exists (select ID from TAB2 where id=f.id);"
