SQL Return every row even if Null values

Question

I want to return all rows from a Contracts table but a second WHERE clause yields only rows which are not null. (In other words in the code below the 'CAD' restriction means approx half the possible rows have no value traded in Canadian Dollars and thus are not returned --whereas I want all possible rows returned showing NULL values where applicable).

I figure it's a Left Self Join but am struggling with the syntax (and/or whether I need to do an Inner Select),

        SELECT  MeasurableID,

                        EntityID,

                        MIN (ContractPrice) AS LowPrice,

                        MAX (ContractPrice) AS HighPrice



        FROM    dbo.Contracts



        WHERE       dbo.Contracts.MeasurableID = 2018

            AND         Contracts.CurrencyCode IN ( 'CAD' ) 



        GROUP BY

            dbo.Contracts.MeasurableID,

            dbo.Contracts.EntityID

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Use conditional aggregation:

SELECT  
  MeasurableID,
  EntityID,
  MIN (CASE WHEN CurrencyCode = 'CAD' THEN ContractPrice END) AS LowPrice,
  MAX (CASE WHEN CurrencyCode = 'CAD' THEN ContractPrice END) AS HighPrice
FROM dbo.Contracts
WHERE MeasurableID = 2018
GROUP BY MeasurableID, EntityID
ORDER BY MeasurableID, EntityID;

SQL Return every row even if Null values

Sign up