Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Noob-type question:

Say I have two tables, candies and colors. The colors table holds just code/value pairs like 01 yellow, 02 blue, 03 green, etc. and is referenced in the candies table by code.

How do I query the candies table for all blue candies without an explicit join? For two years now I've been writing these queries with joins, like:

SELECT * FROM candies a JOIN colors o ON(a.color_code = o.color_code)
WHERE o.color_value = 'blue';

I'm certain every time that I'm bringing a gun to a knife fight, but my googling has been fruitless.

Thanks.

share|improve this question
    
What are you trying to accomplish by avoiding an explicit join? –  Mike Sherrill 'Cat Recall' May 1 '11 at 22:06
    
@Catcall - I suppose I've always assumed the foreign key constraint provides for more than just referential integrity, that applying the constraint lends the user some query-construction advantage that I just couldn't figure out. Just assumed I haven't been taking full advantage of the FK relationship. –  rec.thegeom May 3 '11 at 13:14
    
You said, "Just assumed I haven't been taking full advantage of the FK relationship." And you're planning to learn to take full advantage by not using explicit joins? Don't do that. Explicit joins are a lot more flexible than implicit joins. It's worth your time to learn how they work. –  Mike Sherrill 'Cat Recall' May 3 '11 at 13:42
add comment

2 Answers

up vote 1 down vote accepted

"I'm certain every time that I'm bringing a gun to a knife fight, but my googling has been fruitless." What makes you think that? RDBMSs are built to 'join'

If your aim is to make you SQL more readable then you might prefer the 'using' syntax:

SELECT * FROM candies JOIN colors USING(color_code) WHERE color_value = 'blue';

alternatively, seeing as you are hard-coding 'blue' into your query, you could consider hard-coding the code instead:

SELECT * FROM candies WHERE color_code = '02';

but this will decrease readability unless in your real-world scenario the codes are descriptive (eg 'BLU' for blue)

share|improve this answer
    
Thanks for pointing me toward the 'using' syntax. That is tidier. Using the code from the code/value pairs works out fine except when building reports, especially on aggregate values. Again, I must have been overestimating the power of the FK relationship. –  rec.thegeom May 3 '11 at 13:20
add comment

You can write it as:

select
  ca.*
from
  candies ca
where
  ca.color_code = (
    select co.color_code
    from colors co
    where co.color_value = 'blue'
  )

but there shouldn't be any difference. Actually the join approach is a bit more robust.

What's more - consider getting rid of the colors table at all, and using just color_values instead of codes. It's actually more efficient.

share|improve this answer
    
Or use enums if the colors don't change that often. –  DrColossos Apr 28 '11 at 20:04
    
@DrColossus enums are a pain unless they change very rarely. Things will get a little better in 9.1 –  Jack Douglas Apr 29 '11 at 11:19
    
Thanks for the suggestions. I worry that I might have oversimplified my example as the colors are, in real life, geographic polygons and multi-polygons. Not sure it would make sense to store their geometry-type values repeatedly. –  rec.thegeom May 3 '11 at 13:27
    
Well, neither do I, since I don't know your data distribution and statistics. –  user1593 May 3 '11 at 13:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.