Multi tool use

SQL JOINING ISSUE

I have two different tables but both table may or may not have same records. i need to join these tables and get all the different records for both the tables

for example

CREATE TABLE sql_test_a
(
ID VARCHAR2(4000 BYTE),
FIRST_NAME VARCHAR2(200 BYTE),
LAST_NAME VARCHAR2(200 BYTE)
);

CREATE TABLE sql_test_b
(
ID VARCHAR2(4000 BYTE),
FIRST_NAME VARCHAR2(200 BYTE),
LAST_NAME VARCHAR2(200 BYTE)
);

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('1', 'John', 'Snow');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('2', 'Mike', 'Tyson');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('3', 'Bill', 'Keaton');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('4', 'Greg', 'Mercury');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('5', 'Steve', 'Jobs');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('6', 'Stsdsdve', 'Josdsbs');

INSERT INTO sql_test_b (ID, FIRST_NAME, LAST_NAME) VALUES ('1', 'John', 'Snow');

INSERT INTO sql_test_b (ID, FIRST_NAME, LAST_NAME) VALUES ('2', 'Mike', 'Tyson');

INSERT INTO sql_test_b (ID, FIRST_NAME, LAST_NAME) VALUES ('3', 'Bill', 'Keaton');

INSERT INTO sql_test_b (ID, FIRST_NAME, LAST_NAME) VALUES ('4', 'Greg', 'Mercury');

INSERT INTO sql_test_b (ID, FIRST_NAME, LAST_NAME) VALUES ('5', 'Steve', 'Jobs');

INSERT INTO sql_test_b (ID, FIRST_NAME, LAST_NAME) VALUES ('7', 'Johhny', 'Depp');

INSERT INTO sql_test_b (ID, FIRST_NAME, LAST_NAME) VALUES ('8', 'Johhnaaaay', 'Deaaap');


these are the tables and the records in the tables

and the excepted output should be

ID FIRST_NAME LAST_NAME
1 John Snow
2 Mike Tyson
3 Bill Keaton
4 Greg Mercury
5 Steve Jobs
6 Stsdsdve Josdsbs
7 Johhny Depp
8 Johhnaaaay Deaaap


i tried different join like left outer join, full outer join etc

SELECT a.ID,a.FIRST_NAME,a.LAST_NAME
FROM sql_test_a a left outer join sql_test_b b on a.ID=b.ID
and a.FIRST_NAME=b.FIRST_NAME
and a.LAST_NAME=b.LAST_NAME


this query wont give the exact output

please help

4 Answers
4

The UNION operator returns unique rows of combined queries. So, just use

UNION

SELECT * FROM sql_test_a UNION SELECT * FROM sql_test_b


To filter the result using WHERE clause you could use subquery. For example

WHERE

SELECT *
FROM (SELECT * FROM sql_test_a UNION SELECT * FROM sql_test_b)
WHERE ID > 3


See also live fiddle.

If you use a union you can get the list you need (note that union all could give you duplicate rows depending on your data):

union

union all

select a.id as id, a.first_name as first_name, a.last_name as last_name
from sql_test_a a

union

select b.id as id, b.first_name as first_name, b.last_name as last_name
from sql_test_b b


Using a join is discouraged in this case since it will give you a table with more than three columns, joined on (at least) one of them.

EDIT

You mention you're using oracle. To filter this, you can do several things, among which, one is wrap the query in a temporary table

with tmp as (

select a.id as id, a.first_name as first_name, a.last_name as last_name
from sql_test_a a

union

select b.id as id, b.first_name as first_name, b.last_name as last_name
from sql_test_b b
)

select tmp.id, tmp.first_name, tmp.last_name
from tmp
where
tmp.first_name like '%whatever%';


Try this Please

( SELECT * FROM sql_test_1
) UNION ALL( SELECT * FROM sql_test_b
EXCEPT
SELECT * FROM sql_test_1 )

( SELECT * FROM sql_test_a
MINUS
SELECT * FROM sql_test_b) UNION ALL( SELECT * FROM sql_test_b
MINUS
SELECT * FROM sql_test_a )


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.