Outer join?

Sometimes I need to make queries and join tables, knowing that the
other table rows are not always available.

Example:

I do

SELECT question.author, ..., textbooks.picture
WHERE textbooks.id = questions.tb_id

the problem is that sometimes questions.tb_id is NULL (textbook was
not supplied in question), and what I want to have for
textbook.picture is a NULL. But I want the row returned if it exists
in questions.

How would I do it?

o

> Sometimes I need to make queries and join tables, knowing that the
> other table rows are not always available.
>
> Example:
>
> I do
>
> SELECT question.author, ..., textbooks.picture
> WHERE textbooks.id = questions.tb_id
>
> the problem is that sometimes questions.tb_id is NULL (textbook was
> not supplied in question), and what I want to have for
> textbook.picture is a NULL. But I want the row returned if it exists
> in questions.
>
> How would I do it?

You gave the answer yourself. By using an OUTER JOIN.

Now, joining tables in the WHERE clause has it drawbacks. Today,
there was a post in this forum asking about it -> read "JOIN vs no JOIN"
and you'll get your answer.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On Fri, 19 May 2006 17:18:24 +0200, Martijn Tonies <m.tonies@upscene.removethis.com> wrote:
>> Sometimes I need to make queries and join tables, knowing that the
>> other table rows are not always available.
>>
>> Example:
>>
>> I do
>>
>> SELECT question.author, ..., textbooks.picture
>> WHERE textbooks.id = questions.tb_id
>>
>> the problem is that sometimes questions.tb_id is NULL (textbook was
>> not supplied in question), and what I want to have for
>> textbook.picture is a NULL. But I want the row returned if it exists
>> in questions.
>>
>> How would I do it?
>
> You gave the answer yourself. By using an OUTER JOIN.
>
> Now, joining tables in the WHERE clause has it drawbacks. Today,
> there was a post in this forum asking about it -> read "JOIN vs no JOIN"
> and you'll get your answer.
>
>

Thanks. The query I finally concocted that does what I want, is here:

SELECT
questions.id, userid, tb_id, textbooks.picture
FROM
questions
LEFT JOIN textbooks ON (questions.tb_id = textbooks.id)
ORDER BY date

Do you think that it is sensibly efficient, provided that textbooks.id
is the primary key in textbooks? Textbooks is a relatively small
table, say under 200 entries, questions is bigger, at about 30,000
questions.

I will read join vs. no noin thread now. Thanks.

i

> >> Sometimes I need to make queries and join tables, knowing that the
> >> other table rows are not always available.
> >>
> >> Example:
> >>
> >> I do
> >>
> >> SELECT question.author, ..., textbooks.picture
> >> WHERE textbooks.id = questions.tb_id
> >>
> >> the problem is that sometimes questions.tb_id is NULL (textbook was
> >> not supplied in question), and what I want to have for
> >> textbook.picture is a NULL. But I want the row returned if it exists
> >> in questions.
> >>
> >> How would I do it?
> >
> > You gave the answer yourself. By using an OUTER JOIN.
> >
> > Now, joining tables in the WHERE clause has it drawbacks. Today,
> > there was a post in this forum asking about it -> read "JOIN vs no JOIN"
> > and you'll get your answer.
> >
> >
>
> Thanks. The query I finally concocted that does what I want, is here:
>
> SELECT
> questions.id, userid, tb_id, textbooks.picture
> FROM
> questions
> LEFT JOIN textbooks ON (questions.tb_id = textbooks.id)
> ORDER BY date
>
> Do you think that it is sensibly efficient, provided that textbooks.id
> is the primary key in textbooks? Textbooks is a relatively small
> table, say under 200 entries, questions is bigger, at about 30,000
> questions.

I think it will be efficient, yes.

When in doubt, read the query plan.

> I will read join vs. no noin thread now. Thanks.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On Sun, 21 May 2006 18:07:15 +0200, Martijn Tonies <m.tonies@upscene.removethis.com> wrote:
>
>> >> Sometimes I need to make queries and join tables, knowing that the
>> >> other table rows are not always available.
>> >>
>> >> Example:
>> >>
>> >> I do
>> >>
>> >> SELECT question.author, ..., textbooks.picture
>> >> WHERE textbooks.id = questions.tb_id
>> >>
>> >> the problem is that sometimes questions.tb_id is NULL (textbook was
>> >> not supplied in question), and what I want to have for
>> >> textbook.picture is a NULL. But I want the row returned if it exists
>> >> in questions.
>> >>
>> >> How would I do it?
>> >
>> > You gave the answer yourself. By using an OUTER JOIN.
>> >
>> > Now, joining tables in the WHERE clause has it drawbacks. Today,
>> > there was a post in this forum asking about it -> read "JOIN vs no JOIN"
>> > and you'll get your answer.
>> >
>> >
>>
>> Thanks. The query I finally concocted that does what I want, is here:
>>
>> SELECT
>> questions.id, userid, tb_id, textbooks.picture
>> FROM
>> questions
>> LEFT JOIN textbooks ON (questions.tb_id = textbooks.id)
>> ORDER BY date
>>
>> Do you think that it is sensibly efficient, provided that textbooks.id
>> is the primary key in textbooks? Textbooks is a relatively small
>> table, say under 200 entries, questions is bigger, at about 30,000
>> questions.
>
> I think it will be efficient, yes.
>
> When in doubt, read the query plan.

I will definitely try messing with query plans. Thank you very much.

I just say

EXPLAIN SELECT ...

is that right?

i

>> I will read join vs. no noin thread now. Thanks.
>
>

> > I think it will be efficient, yes.
> >
> > When in doubt, read the query plan.
>
> I will definitely try messing with query plans. Thank you very much.
>
> I just say
>
> EXPLAIN SELECT ...
>
> is that right?

Yes.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com