Re: Cross join-question: what to do when there's a null?

On 6 August 2010 16:26, Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> wrote:
> Consider this small table:
>
> ecdb=> SELECT id, name, package FROM components;
>  id | name | package
> ----+------+---------
>  1 |    1 |       2
>  2 |    2 |
> (2 rows)
>
> The values in name and package refer to two other tables. I would like
> to extract the name in the same query, so I run a cross-join:
>
> ecdb=> SELECT c.id, x.name, c.package FROM components c,
> component_names x WHERE c.id = x.id;
>  id |  name  | package
> ----+--------+---------
>  1 | BC547  |       2
>  2 | 1N4148 |
> (2 rows)
>
>
> Next, I would also like the package name to be shown. However, since
> there's a null (which is perfectly legal in this case) I am unable to
> get it to work:
>
> ecdb=> SELECT c.id, x.name, y.name FROM components c, component_names
> x, packages y WHERE c.id = x.id AND c.package = y.id;
>  id | name  | name
> ----+-------+------
>  1 | BC547 | SO
> (1 row)
>
>
> The result I'm looking for is this:
>
>  id |  name  | package
> ----+--------+---------
>  1 | BC547  |       SO
>  2 | 1N4148 |
> (2 rows)
>
> How do I run a cross join like this that will include the null element?
>

Would this work?

SELECT c.id, x.name, y.name
FROM components c
INNER JOIN component_names x ON c.id = x.id
LEFT JOIN packages y ON c.package = y.id;

It's recommended to use JOIN clauses to join your tables as it makes
it easier to understand where they join.  In the case above, it will
only return rows from "components" if there's a corresponding row in
"component_names" because of the INNER JOIN.  The LEFT JOIN basically
only gets rows from "packages" if there is a matching row, but doesn't
require it.

--
Thom Brown
Registered Linux user: #516935