OraFAQ Forum: SQL & PL/SQL » SQL Query to fetch not matching records

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query to fetch not matching records (12 C Windows NT)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Query to fetch not matching records [message #650722]

Mon, 02 May 2016 04:28

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
I have a table table_A and table_B.
Both have two columns material_number and language_code which is composite primary key.
The table_A has 1.5 million records and table_B has short of 6 records to 1.5 million.
I want to find out the missing 6 records using these tables.
How do I achieve this? Is NOT EXISTS will work?
kindly suggest me.

thanks.

Regards,
pstanand.

Report message to a moderator

Re: SQL Query to fetch not matching records [message #650723 is a reply to message #650722]

Mon, 02 May 2016 04:35

_jum
Messages: 577
Registered: February 2008

Senior Member

You could use

SELECT material_number, language_code FROM table_A
MINUS
SELECT material_number, language_code FROM table_B

Report message to a moderator

Re: SQL Query to fetch not matching records [message #650724 is a reply to message #650722]

Mon, 02 May 2016 05:02

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Sure, using NOT EXISTS will give you an antijoin that will work.

Whether this would be more or less efficient than jum's solution, I don't know. I would expect both to be implemented with index full scans.

Report message to a moderator

Re: SQL Query to fetch not matching records [message #650731 is a reply to message #650724]

Mon, 02 May 2016 07:43

Bill B
Messages: 1971
Registered: December 2004

Senior Member

and if you wanted to put the missing records into table_b you could do the following

INSERT INTO TABLE_B
SELECT * FROM TABLE_A
WHERE (MATERIAL_NUMBER,LANGUAGE_CODE) IN
(SELECT material_number, language_code FROM table_A
MINUS
SELECT material_number, language_code FROM table_B);

Report message to a moderator

Re: SQL Query to fetch not matching records [message #650750 is a reply to message #650731]

Tue, 03 May 2016 01:45

muhammadadnan198@gmail.co
Messages: 2
Registered: May 2016
Location: Pakistan

Junior Member

SELECT table_A.material_number, table_A.language_code
FROM table_A right outer join table_B
on table_A.material_number = table_B.material_number
and table_A.language_code = table_B.language_code
where table_B.material_number is null

============================================
just copy and past see results

[Updated on: Tue, 03 May 2016 01:47]

Report message to a moderator

Re: SQL Query to fetch not matching records [message #650752 is a reply to message #650750]

Tue, 03 May 2016 02:10

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

@muhammadadnan198@gmail.co

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Previous Topic:	HELP TO FORM DYNAMIC INSERT QUERY IN PL/SQL
Next Topic:	Alphanumeric Sequence Generator

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 23:27:52 CDT 2024