Postgresql – provide a default for a left outer join

join;postgresql

Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join

SELECT *
FROM a LEFT OUTER JOIN b
ON a.a1 = b.b1

Then b1 and b2 will be NULL where a value of a1 has no matching value of b1.

Can I provide a default value for b2, instead of NULL? Note that COALESCE won't work here, because I don't want the default value to override potential NULLs in b2 where there is a value of b1 matching a1.

That is, with a and b as

CREATE TABLE a (a1)
  AS VALUES (1),
            (2),
            (3) ;

CREATE TABLE b (b1,b2)
  AS VALUES (1, 10),
            (3, null) ;


a1     b1 | b2
---    --------
 1      1 | 10
 2      3 | NULL
 3

and a default for b2 of, say, 100, I want to get the result

a1 | b1   | b2
---------------
1  |  1   | 10
2  | NULL | 100
3  |  3   | NULL

In this simple case I could do it "by hand" by looking at whether b1 is NULL in the output. Is that the best option in general, or is there a more standard and neater way?

Best Answer

SELECT a.a1,b.b1,  
    CASE WHEN b.b1 is NULL THEN 5 ELSE b.b2 END AS b2  
FROM a LEFT OUTER JOIN b  
ON a.a1 = b.b1

Related Solutions

Sql-server – Outer Apply vs Left Join Performance

Can anyone tell how exactly apply works and how will it effect the performance in very large data

APPLY is a correlated join (called a LATERAL JOIN in some products and newer versions of the SQL Standard). Like any logical construction, it has no direct impact on performance. In principle, we should be able to write a query using any logically equivalent syntax, and the optimizer would transform our input into exactly the same physical execution plan.

Of course, this would require the optimizer to know every possible transformation, and to have the time to consider each one. This process might well take longer than the current age of the universe, so most commercial products do not take this approach. Therefore, query syntax can, and often does, have an impact on final performance, though it is difficult to make general statements about which is better and why.

The specific form of OUTER APPLY ( SELECT TOP ... ) is most likely to result in a correlated nested loops join in current versions of SQL Server, because the optimizer does not contain logic to transform this pattern to an equivalent JOIN. Correlated nested loops join may not perform well if the outer input is large, and the inner input is unindexed, or the pages needed are not already in memory. In addition, specific elements of the optimizer's cost model mean a correlated nested loops join is less likely than a semantically-identical JOIN to produce a parallel execution plan.

I was able to make same query with single left join and row_number()

This may or may not be better in the general case. You will need to performance test both alternatives with representative data. The LEFT JOIN and ROW_NUMBER certainly has potential to be more efficient, but it depends on the precise query plan shape chosen. The primary factors that affect the efficiency of this approach is the availability of an index to cover the columns needed, and to supply the order needed by the PARTITION BY and ORDER BY clauses. A second factor is the size of the table. An efficient and well-indexed APPLY can out-perform a ROW_NUMBER with optimal indexing if the query touches a relatively small portion of the table concerned. Testing is needed.

Mysql – Simple left outer join leaving me with inner join

I think you need a UNION or an OR with EXISTS:

SELECT c.* 
FROM change AS c
WHERE c.project_id = 10
   OR c.project_id IS NULL
  AND EXISTS
        ( SELECT *
          FROM change AS x 
          WHERE x.project_id = 10
            AND x.object_id = c.object_id
        ) ;

Best Answer

Related Solutions

Sql-server – Outer Apply vs Left Join Performance

Mysql – Simple left outer join leaving me with inner join

Related Question