Postgres INNER JOIN

Question

In my query, I want to join tables only if the first table's specified column is null. How I can do the join. This is an INNER JOIN and there are 3 tables.

Here is my query.

SELECT item_id,item_name
  FROM item i
INNTER JOIN supplier s ON i.item_id=s.items_id
INNER JOIN order O ON O.id=s.req_id
WHERE price>400 AND category='ALL';

this join should be available only if the column 'item_order_id' in the item table is NULL. If that column is not null, this join shuold not be done. How can I do in in Postgres (I use postgres 8).

"Postgres 8" covers five major releases (8.0, 8.1, 8.2, 8.3 and 8.4) - you should state your exact version. Also note that only 8.4 is still supported (but only for a few more months) so you should really plan an upgrade to a current (e.g. 9.3) release. — user330315
– user330315, Commented Nov 6, 2013 at 11:19
I use 8.0.3. versioning upgrade can be a good idea. but for my system environment I have to keep current version. — Débora
– Débora, Commented Nov 6, 2013 at 11:23

Klas Lindbäck · Accepted Answer · 2013-11-06 11:17:06Z

2

Option 1. Put the condition in the WHERE clause:

SELECT item_id,item_name
  FROM item i
INNER JOIN supplier s ON i.item_id=s.items_id
INNER JOIN order O ON O.id=s.req_id
WHERE price>400 AND category='ALL' AND i.item_order_id IS NULL;

Option 2. Put the condition in the join:

SELECT item_id,item_name
  FROM item i
INNER JOIN supplier s ON i.item_id=s.items_id AND i.item_order_id IS NULL
INNER JOIN order O ON O.id=s.req_id
WHERE price>400 AND category='ALL' ;

answered Nov 6, 2013 at 11:17

Klas Lindbäck

33.4k5 gold badges61 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Débora Over a year ago

thanks for reply. How ever,I am using this query inside a function. This join should be happened only if the item_order_id is null. Can't we put this joining inside the where clause ? I means some thing like this, inside the function and in the where clause CASE when item_order_id IS NULL ..

Klas Lindbäck Over a year ago

I don't understand what your concern is. Option 2 joins only those records where item_order_id is null. Both options produce the same result. There may be performance issues, so if that is your concern you should check the explain plan for both options (against fully loaded tables).

wildplasser Over a year ago

@KlasLindbäck: no there are no performance issues. The two queries will generate exactly the same plan(s).

Collectives™ on Stack Overflow

Postgres INNER JOIN

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related