Don't return row when all values are NULL - SQL

Question

I am using redshift, but I suspect this will be valuable to many other forms of SQL. I am also new to SQL and would appreciate any advice on my query, other than advice which pertains specifically to my question; just please keep it constructive.

QUESTION: How do I avoid returning rows where all rows of my result are NULL?

Here is my query:

SELECT 
    CASE WHEN events.event_id IN 
        (SELECT DISTINCT event_id FROM strings_ref WHERE string_id = 123)
        THEN event_id END AS 123, 
    CASE WHEN events.event_id IN 
        (SELECT DISTINCT event_id FROM strings_ref WHERE string_id = 456)
        THEN event_id END AS 456
    FROM events

There are many many other string_ids, and this query just returned 1000 consecutive NULLS. EDIT: I have verified that there are results.

NOTE: I have accepted the answer that most pertains to my question, but @gordon-linoff 's answer is more general.

I think this would be much clearer if you shared some sample data and your desired results. Your query is very strange and could be solved simply by aggregating and using a CASE statement instead of subquerying to return the same thing you are returning anyway. — JNevill
– JNevill, Commented Jul 9, 2018 at 18:21

Ctznkane525 · Accepted Answer · 2018-07-09 18:26:47Z

1

Here's one way:

SELECT E.*, SR1.EVENT_ID, SR2.EVENT_ID
FROM Events E
    LEFT OUTER JOIN strings_ref sr1 on E.EVENT_ID=SR1.EVENT_ID AND sr1.string_id = 123
    LEFT OUTER JOIN strings_ref sr2 on E.EVENT_ID=SR2.EVENT_ID AND sr2.string_id = 456
WHERE SR1.EVENT_ID IS NOT NULL OR SR2.EVENT_ID IS NOT NULL

Join on strings_ref multiple times

answered Jul 9, 2018 at 18:26

Ctznkane525

7,5453 gold badges19 silver badges41 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2018-07-09 18:20:35Z

1

One method uses a subquery:

SELECT e.*
FROM (SELECT (CASE WHEN e.event_id IN (SELECT sr.event_id FROM strings_ref sr WHERE sr.string_id = 123)
                   THEN e.event_id
              END) AS e_123, 
             (CASE WHEN e.event_id IN (SELECT sr.event_id FROM strings_ref sr WHERE sr.string_id = 456)
                  THEN e.event_id
              END) AS e_456
      FROM events e
     ) e
WHERE e_123 IS NOT NULL or e_456 IS NOT NULL;

Some advice:

Qualify all column names in the query.
Use table aliases that are abbreviations of the table name.
SELECT DISTINCT is not needed with IN.
Give your columns names that don't look like numbers.

answered Jul 9, 2018 at 18:20

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

ChootsMagoots Over a year ago

Thanks,My theory with the DISTINCT clause was to speed up processing the IN condition. Is the DISTINCT likely to take more time than it saves?

Gordon Linoff Over a year ago

@ChootsMagoots . . . It is immaterial. Let the compiler decide the best approach for handling the IN.

Collectives™ on Stack Overflow

Don't return row when all values are NULL - SQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related