Home > mailing lists

Getting non_NULL right-side values on a non-matching join? - Mailing list pgsql-general

From	Ken Tanzer
Subject	Getting non_NULL right-side values on a non-matching join?
Date	November 22, 2013 00:26:33
Msg-id	CAD3a31XQCYnAOw590B7qcNhCRfC-QXuNev4qzp7Xj+Y4BzspMQ@mail.gmail.com Whole thread Raw
Responses	Re: Getting non_NULL right-side values on a non-matching join? Re: Getting non_NULL right-side values on a non-matching join?
List	pgsql-general

Tree view

Hello. In doing a left join with a particular view as the right table, and non-matching join criteria, I am getting values returned in a few fields. All the rest are NULL. I would expect all the right side values to be NULL.

(The view is large and messy, but it doesn't seem like that should matter. I've attached the create statement for the view anyway.)

Am I missing something really really obvious about LEFT JOINs here? This statement seems to confirm my expectations:

"This query is called a left outer join because the table mentioned on the left of the join operator will have each of its rows in the output at least once, whereas the table on the right will only have those rows output that match some row of the left table. When outputting a left-table row for which there is no right-table match, empty (null) values are substituted for the right-table columns."

http://www.postgresql.org/docs/9.2/static/tutorial-join.html

Thanks in advance.

Ken

-- Nothing special about the -1 id here, just an example of a non-matching value.

ag_spc=> SELECT foo.client_id AS foo_id,rent_info.client_id AS ri_id,move_in_type,annual_income,monthly_income_total,dependent_count from (SELECT -1 AS client_id) foo LEFT JOIN rent_info USING (client_id);

--------+-------+--------------+---------------+----------------------+-----------------

-1 | | Move-in | 0 | 0 | 0

(1 row)

ag_spc=> SELECT * FROM rent_info WHERE client_id = -1;

----------------+--------------------+-------------------------------+--------------------+-----------------+--------------+----------+------------------+------------------+-----------+----------------------+--------

---------------+-------------------+----------------+--------------------+------------------+-------------------------+-----------------+------------------------------+--------------+---------------+---------------+-

--------------+---------------------+-----------------------+-------------------+-------------------+------------------+---------------------+----------------+-----------------+---------+-----------+-------------+---

--------------+---------------+----------------------+------------------------+---------------------+--------------------------+-----------------------+-------------------------+----------------------+---------------

----------+------------------------+--------------------------------+------------+---------------------+-----------------+----------------+---------------------+---------------+-----------------+--------------------+

-------------------+-----------------------+----------------+------------+--------------+----------------------+----------------------+----------------+------------------------+-------------------------+-------------

--------------+-------------------------------+---------------------+-----------+----------------------+---------------+--------------------------+------------------------+-------------------+------------------------

+------------------+------------------+---------------------------+-----------------+-----+--------------+------------------+-------------------+-------------+--------------+--------------+----------------+----------

(0 rows)