Question:- Suppose there are two tables A and B and we need to write 3 SQL queries which returns the record set as shown in the below figure.
First Query:-
In this case, you have to write a SQL Query using SQL JOIN which return the data which is not common to these two tables A& B as shown in the shaded part in 1st figure.
Second Query:-
In this case, you have to write a SQL Query using SQL JOIN which will return the data from table A which is not in the table B as shown as shaded part in 2nd figure.
Third Query:-
In this case, you have to write a SQL Query using SQL JOIN which will return the data from table B which is not in the table A as shown as shaded part in 3rd figure.
Previous Questions and their Answers
Question of the Week – 2/1/2014 – Question 1
Answer
Question of the Week – 28/12/2013 – Question 2
I think this will work:
CREATE TABLE A (ID INT )
CREATE TABLE B (ID INT)
INSERT INTO A VALUES(1),(2),(3),(4)
INSERT INTO B VALUES(4),(5),(6),(7)
First Query:-
SELECT * FROM A WHERE A.ID NOT IN(SELECT * FROM A INTERSECT SELECT * FROM B)
UNION
SELECT * FROM B WHERE B.ID NOT IN (SELECT * FROM A INTERSECT SELECT * FROM B)
SECOND Query:-
SELECT * FROM A WHERE A.ID NOT IN(SELECT * FROM A INTERSECT SELECT * FROM B)
Third Query:-
SELECT * FROM B WHERE B.ID NOT IN (SELECT * FROM A INTERSECT SELECT * FROM B)
Good Answer Vimal
First Query:
(SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM B EXCEPT SELECT * FROM A)
SECOND Query:-
(SELECT * FROM A EXCEPT SELECT * FROM B) EXCEPT (SELECT * FROM B EXCEPT SELECT * FROM A)
Third Query:-
(SELECT * FROM B EXCEPT SELECT * FROM A) EXCEPT (SELECT * FROM A EXCEPT SELECT * FROM B)
[db2inst1@Reporter_Dev1 Gaurav]$ db2 -tvf createTable.sql
CREATE TABLE A ( ID INTEGER , VALUE VARCHAR(10) ) organize by column
DB20000I The SQL command completed successfully.
CREATE TABLE B ( ID INTEGER , VALUE VARCHAR(10) ) organize by column
DB20000I The SQL command completed successfully.
[db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into a values (1, 'One')"
DB20000I The SQL command completed successfully.
[db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into a values (2, 'Two')"
DB20000I The SQL command completed successfully.
[db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into a values (3, 'Three')"
DB20000I The SQL command completed successfully.
[db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into b values (3, 'Three')"
DB20000I The SQL command completed successfully.
[db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into b values (4, 'Four')"
DB20000I The SQL command completed successfully.
[db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into b values (5, 'Five')"
DB20000I The SQL command completed successfully.
[db2inst1@Reporter_Dev1 Gaurav]$ db2 "Select distinct * from A where A.ID not in (select A.ID from A inner join B ON A.ID = B.ID) UNION Select distinct * from B where B.ID not in (select A.ID from A inner join B ON A.ID = B.ID)"
ID VALUE
———– ———-
1 One
2 Two
4 Four
5 Five
4 record(s) selected.
[db2inst1@Reporter_Dev1 Gaurav]$ db2 "Select distinct * from A where A.ID not in (select A.ID from A inner join B ON A.ID = B.ID) "
ID VALUE
———– ———-
1 One
2 Two
2 record(s) selected.
[db2inst1@Reporter_Dev1 Gaurav]$ db2 "Select distinct * from B where B.ID not in (select A.ID from A inner join B ON A.ID = B.ID)"
ID VALUE
———– ———-
4 Four
5 Five
2 record(s) selected.
Hi Vimal
Again very good answer with good use of Except command. Keep the good work going..
Good Answer Gaurav…. keep it up
CREATE TABLE A (ID INT )
CREATE TABLE B (ID INT)
INSERT INTO A VALUES(1),(2),(3),(4)
INSERT INTO B VALUES(4),(5),(6),(7)
QUERY 1 :
select A.ID from A left join B on A.ID = B.ID where B.ID is null
union
select B.ID from B left join A on B.ID = A.ID where A.ID is null
Query 2 : select A.ID from A left join B on A.ID = B.ID where B.ID is null
Query 3: select B.ID from B left join A on B.ID = A.ID where A.ID is null
Hi Saptarshi,
really very good answer with good use of Joins. Keep the good work going
Thanks to all of you for Giving the correct Answers…
Pingback: JDOt-Ova-Flowin
Pingback: fun88
Pingback: ww88 casino
Pingback: MyWeb
Pingback: Shoarmazaak hengelo
Pingback: Crystal Meth kaufen
Pingback: Spray Drying Equipment Market Projections Analysis 2017 – 2027
Pingback: Drogen kaufen im Darknet
Pingback: https://novushub.com/hindi-gana-video/
Pingback: ซีเกมส์ 2019
Pingback: indoqqpoker
Pingback: cdrqq
Pingback: middle tennessee vs western kentucky live
Pingback: www.nuguru.info
Pingback: kompasqq
Pingback: www.ratucapsa1.club
Pingback: senangpoker
Pingback: elang qq
Pingback: ร้านเช่าชุดราตรี
Pingback: 12bet link
Pingback: iPhone XR Screen Replacement for LCD Digitizer Amazon Best Seller
Pingback: dang nhap w88