OraFAQ Forum: SQL & PL/SQL » how to get unmatched record from emp and dept tables

Home » SQL & PL/SQL » SQL & PL/SQL » how to get unmatched record from emp and dept tables (oracle 10g)

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

how to get unmatched record from emp and dept tables [message #635391]

Sun, 29 March 2015 08:48

ursfriend77
Messages: 6
Registered: February 2011
Location: Bangalore

Junior Member

Hi,

I want to get unmatched record from emp and dept tables. This is interview question,i tried using full outer join but using full outer i was getting matched and unmatched record,but my scenario is i have to get only unmatched record my 2 tables(emp and dept)

Thanks
Venki

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635392 is a reply to message #635391]

Sun, 29 March 2015 08:49

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

MINUS set operator might do that (if I correctly understood the question).

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635393 is a reply to message #635392]

Sun, 29 March 2015 08:52

ursfriend77
Messages: 6
Registered: February 2011
Location: Bangalore

Junior Member

I tried like this
select * from emp
minus
select * from dept
but i am getting below error:

ORA-01789: query block has incorrect number of result columns

Thanks
Venki

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635395 is a reply to message #635393]

Sun, 29 March 2015 09:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

MINUS require same number of columns of same datatype in the same order.

What exactly do you mean by "unmatched" records?

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635396 is a reply to message #635391]

Sun, 29 March 2015 09:20

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

Senior Member

I am not sure what you want as a result. But it sounds to me as though you may need a predicate with a WHERE NOT EXISTS subquery.

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635397 is a reply to message #635391]

Sun, 29 March 2015 09:33

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can do it with MINUS, NOT EXISTS, Join and many other less effective ways like COUNT.

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635399 is a reply to message #635397]

Sun, 29 March 2015 10:55

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

>This is interview question

Please post the exact interview question.

Also, post a test case.

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635400 is a reply to message #635399]

Sun, 29 March 2015 11:14

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why a test case? This is the standard EMP and DEPT tables!
The test case is in ?/rdbms/admin/scott.sql and utlsampl.sql scripts.

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635433 is a reply to message #635400]

Mon, 30 March 2015 05:16

vippysharma
Messages: 73
Registered: May 2013
Location: www

Member

@ursfriend77..

Use MINUS set operator but do remember, Both tables should have same no of columns. (if mismatch use null as column)

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635435 is a reply to message #635433]

Mon, 30 March 2015 05:22

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Not both tables but both queries result sets and not only the number but also the data types.

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635437 is a reply to message #635433]

Mon, 30 March 2015 05:27

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

vippysharma wrote on Mon, 30 March 2015 15:46

@ursfriend77..

Use MINUS set operator but do remember, Both tables should have same no of columns.

It is already suggested by others above.

Quote:

if mismatch use null as column

Wouldn't that return all the rows for the first query, unless there is an exact match for the NULL values for the columns mentioned in the column list in the respective order in both queries. Or, vice-versa, you could get rows filtered out for the NULL values.

The number of columns and their data types need to be same in the column_list of the SELECT query.

For example,

SQL> SELECT deptno FROM emp
  2  MINUS
  3  SELECT deptno FROM dept
  4  /

no rows selected

But, if you just add NULL as you said, it would return different output:

SQL> SELECT deptno, comm FROM emp
  2  MINUS
  3  SELECT deptno, NULL FROM dept
  4  /

    DEPTNO       COMM
---------- ----------
        30        300
        30        500
        30       1400

SQL>

So, you just need to maintain the matching number of columns and their datatypes in correct order in both the queries, not that tables need to have same number of columns.

Edit : Oops, I edited using back button so two posts got submitted. Deleting the duplicate post.

[Updated on: Mon, 30 March 2015 05:41]

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635438 is a reply to message #635437]

Mon, 30 March 2015 05:52

vippysharma
Messages: 73
Registered: May 2013
Location: www

Member

@Lalit ..
Yeh adding NULL give inappropriate result & Columns as well as Datatypes shold also be same for both tabels.
(There is Incomplete answer by me.Apologize. )

Cheers Smile

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635552 is a reply to message #635438]

Wed, 01 April 2015 00:45

saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai

Junior Member

Hi all,

by using minus we get the unmatched rows of the first table only but he is asking for both the tables

(SELECT deptno, comm FROM emp
minus
SELECT deptno, comm FROM dep)
union all
(SELECT deptno, comm FROM dep
minus
SELECT deptno, comm FROM emp);

This will help

Report message to a moderator

Re: how to get unmatched record from emp and dept tables [message #635554 is a reply to message #635552]

Wed, 01 April 2015 01:19

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ This is not DEP but DEPT
2/ There is no COMM column in DEPT
3/ This is your interpretation of the question
4/ Given there is FK on EMP referencing DEPT, there is no rows in EMP which does not match one in DEPT (assuming DEPTNO can't be NULL and I doubt you think about NULL when writing your query)
5/ "This will help ", by your errors, as for your previous answers, definitively!

And forgot the moderator bit:
Please read How to use [code] tags and make your code easier to read.

[Updated on: Wed, 01 April 2015 01:19]

Report message to a moderator

Previous Topic:	different results of sql query on different servers (with the same data)
Next Topic:	Best way to get count of unique months with activity

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 27 01:20:22 CDT 2026