SQL Query Help

[Kntx]

Hello,

I am writing an SQL query. Maybe I am having a brain fart. Maybe I just don't know what I'm doing. Whatever.

Here is a generic representation of what I'm trying to accomplish.

TABLE A
~~~~~~
ID
OTHER...

TABLE B
~~~~~~
ID
FK_A
OTHER...

So A has a one to many relationship to B. I want to get all records of A that have child rows in B. So I think, OK, join. But of course this approach gives me duplicate records of table A for as many child records exist in B. I am not interested in any data in table B. Just A. Just those records that have one or many child records. No records that don't have child records.

Note, that I have accomplished this by using multiple queries, the distinct clause and temporary tables. What I really want though, is a single query.

Thoughts?

 

[clamum]

Is this what you're looking for?

 

[Gunslinger08]

 

[mugs]

Yeah, select distinct would do what you're looking for. You generally want to avoid using DISTINCT, but in this case I'm not sure if there is a more efficient way to do it.

Other possibilities are:
SELECT *
FROM a
WHERE a.id IN (select fk_a from b)

or

SELECT *
FROM a
WHERE a.id IN (select fk_a from b group by fk_a)

or even

SELECT *
FROM a
WHERE a.id IN (select distinct fk_a from b)

Yeah I know I said it's best to avoid distinct, but it could make a difference if it's at a different point in the execution process. Depending on your RDBMS and whether you have an index on b.fk_a, those last two options might be more efficient than joining and then doing a select distinct. Note that I am not a DBA and I am not an expert on query efficiency, this is just me thinking logically that a group by or a distinct on an indexed column might be faster. As for the first one, I'm not sure if that would be faster than a select distinct or not, but I have had surprising efficiency from queries formatted like that.

In any case, my post serves to highlight that there are often several ways to do something in SQL, and what seems like the best way or the most obvious way may not always be the most efficient way.

 

[mugs]

FWIW, I ran the equivalent of all of the above queries on a pair of tables of work where the parent table has 32k records and the child table has 42 million.

Execution times:
select id from a where id IN (select fk_a from b);
Elapsed: 00:02:07.40


select distinct a.id from a, b where a.id = b.fk_a;
Elapsed: 00:06:23.54


select id from a where id IN (select fk_a from b group by fk_a);
Elapsed: 00:04:45.27


select id from a where id IN (select distinct fk_a from b);
Elapsed: 00:01:55.62

The order I executed them is the order that is shown. I executed my first query first to try to ensure that it didn't benefit from any caching. I suspect my last query (the distinct in the subquery) may have benefited from caching of the query before it, because they are essentially the same.

This is in Oracle by the way (9i to be precise). No doubt the results could be different in SQL Server or MySQL or PostgreSQL or what have you... or even in a more recent version of Oracle.

Moral of the story is, respect your DBA, because he has to know this stuff.

Edit: In the example above, there IS an index on b.fk_a

 

[troytime]

Originally posted by: mugs
FWIW, I ran the equivalent of all of the above queries on a pair of tables of work where the parent table has 32k records and the child table has 42 million.

Execution times:
select id from a where id IN (select fk_a from b);
Elapsed: 00:02:07.40


select distinct a.id from a, b where a.id = b.fk_a;
Elapsed: 00:06:23.54


select id from a where id IN (select fk_a from b group by fk_a);
Elapsed: 00:04:45.27


select id from a where id IN (select distinct fk_a from b);
Elapsed: 00:01:55.62

The order I executed them is the order that is shown. I executed my first query first to try to ensure that it didn't benefit from any caching. I suspect my last query (the distinct in the subquery) may have benefited from caching of the query before it, because they are essentially the same.

This is in Oracle by the way (9i to be precise). No doubt the results could be different in SQL Server or MySQL or PostgreSQL or what have you... or even in a more recent version of Oracle.

Moral of the story is, respect your DBA, because he has to know this stuff.

Edit: In the example above, there IS an index on b.fk_a

nice post!

what platform is that from?
also, whats the average number of childs per row? (in the IN statement or in the subquery)

 

[Kntx]

Originally posted by: mugs
FWIW, I ran the equivalent of all of the above queries on a pair of tables of work where the parent table has 32k records and the child table has 42 million.

Execution times:
select id from a where id IN (select fk_a from b);
Elapsed: 00:02:07.40


select distinct a.id from a, b where a.id = b.fk_a;
Elapsed: 00:06:23.54


select id from a where id IN (select fk_a from b group by fk_a);
Elapsed: 00:04:45.27


select id from a where id IN (select distinct fk_a from b);
Elapsed: 00:01:55.62

The order I executed them is the order that is shown. I executed my first query first to try to ensure that it didn't benefit from any caching. I suspect my last query (the distinct in the subquery) may have benefited from caching of the query before it, because they are essentially the same.

This is in Oracle by the way (9i to be precise). No doubt the results could be different in SQL Server or MySQL or PostgreSQL or what have you... or even in a more recent version of Oracle.

Moral of the story is, respect your DBA, because he has to know this stuff.

Edit: In the example above, there IS an index on b.fk_a

You're the man. Now on Monday I can write my query!