SQL problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL problem

Author

Topic

darius12
Starting Member

16 Posts

Posted - 2012-01-20 : 13:52:51

I have a table with persons, primary key is id, and some of persons have mothers and fathers and i'm using id of fathers and mothers as foreign key.

CREATE TABLE person (
id NUMBER(2) PRIMARY KEY,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(13) NOT NULL,
father NUMBER(3) REFERENCES person
mother NUMBER(3) REFERENCES person,
born DATE NOT NULL,
died DATE,
height NUMBER(3)
);

if i wanna select all people who have children (whoes id DOES appear in father or mother column) i would use this code

SELECT first_name, last_name FROM person WHERE id IN ((SELECT father FROM person) UNION (SELECT mother FROM person))

but if i wanna select all people without children i try to put NOT IN insted of IN and the code doesnt work...Any help?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 14:24:11

Perhaps you can do it using NOT EXISTS clause, like this?

SELECT
	first_name,
	last_name
FROM
	person p
WHERE
	NOT EXISTS
(
	SELECT * 
	FROM person p2 
	WHERE p2.father = p.id OR p2.mother = p.id
);

darius12
Starting Member

16 Posts

Posted - 2012-01-20 : 14:27:59

Great answer, thanks a lot!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 14:40:29

You are very welcome .)

Just FYI, it may be more efficient to use a similar EXISTS clause in your first query as well, where you are trying to find people who have children.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-20 : 15:36:06

The IN and EXISTS constructs perform the same when the subqueries are of the same form, same execution plans, same performance characteristics.

Edit: clarified

--
Gail Shaw
SQL Server MVP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 16:11:40

[code]CREATE TABLE person
(
id INT PRIMARY KEY,
father INT REFERENCES person(id),
mother INT REFERENCES person(id)
);

INSERT INTO person VALUES (1,NULL,NULL),(2,NULL,NULL),(3,1,2),(4,1,2);
GO

SELECT * FROM person WHERE id IN (SELECT father FROM person UNION SELECT mother FROM person);
SELECT * FROM person p1 WHERE EXISTS (SELECT * FROM person p2 WHERE p2.father = p1.id OR p2.mother=p1.id);

GO

DROP TABLE person;[/code]In the execution plan for the first select, I see three clustered index scans, in the second I see two. Relative costs 54% and 46%.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-20 : 16:50:10

That's because one has a Union in the subquery (which requires two separate scans of the table) and one has an OR in the subquery (which requires one single scan of the table).

It's not the IN vs exists itself, rather the semantics within the subquery. If the IN could be rewriten so that its subquery was a single select (which I'm not sure it can in this particular case) or the exist to use a union (which would be a poor choice), then there wouldn't be any difference

eg

50/50 each, three scans apiece

SELECT * FROM person WHERE id IN (SELECT father FROM person UNION SELECT mother FROM person);

SELECT * FROM person p1 WHERE EXISTS (SELECT 1 FROM person p2 WHERE p2.father = p1.id UNION SELECT 1 FROM person p2 WHERE p2.mother=p1.id);

I've clarified my previous post, which was vaguer than it should have been, my apologies for that.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources