Professional Web Applications Themes

Outer join? - MySQL

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...

  1. #1

    Default 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

    Ignoramus23298 Guest

  2. #2

    Default Re: 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?
    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
    [url]http://www.upscene.com[/url]
    My thoughts:
    [url]http://blog.upscene.com/martijn/[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

  3. #3

    Default Re: Outer join?

    On Fri, 19 May 2006 17:18:24 +0200, Martijn Tonies <m.toniesupscene.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

    Ignoramus23298 Guest

  4. #4

    Default Re: 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?
    > >
    > > 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
    [url]http://www.upscene.com[/url]
    My thoughts:
    [url]http://blog.upscene.com/martijn/[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

  5. #5

    Default Re: Outer join?

    On Sun, 21 May 2006 18:07:15 +0200, Martijn Tonies <m.toniesupscene.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.
    >
    >
    Ignoramus24136 Guest

  6. #6

    Default Re: Outer join?

    > > 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
    [url]http://www.upscene.com[/url]
    My thoughts:
    [url]http://blog.upscene.com/martijn/[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

Similar Threads

  1. left outer join problem
    By softie in forum MySQL
    Replies: 5
    Last Post: August 31st, 09:27 PM
  2. Left Outer Join
    By Jared in forum MySQL
    Replies: 0
    Last Post: May 3rd, 03:31 AM
  3. OUTER JOIN
    By jorgepino in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 23rd, 12:59 PM
  4. FULL OUTER JOIN
    By beckydub in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 13th, 02:54 PM
  5. SQL query with a Left outer Join
    By Ad Bec in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 22nd, 04:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139