SQL - Set certain columns to null based on condition

Question

Say I have a table named people with various attributes

first name
last name
age
phone
email
address
...

I would like to make a query that will return all people, however, under a certain condition (a separate subquery) I would like for certain columns of the select statement to be set to null for redacting purposes.

So, say I have the basic query:

SELECT first_name, last_name, age, phone, address
FROM people;

Which would return a table similar to:

|first_name|last_name|age|phone|address       |
------------------------------------------------
|Joe       |Someone  |12 |123  |1234 Somewhere|
|Bob       |Other    |45 |982  |4321 Somewhere|

I would like to be able to do something like this:

SELECT
    first_name,
    last_name,
    if(people.field IN (
        SELECT other_table.some_id FROM other_table WHERE other_table.field = 'is good')
    )
        age, phone, address
    else
        NULL
FROM people;

With the result I would like to have:

|first_name|last_name|age  |phone|address       |
------------------------------------------------
|Joe       |Someone  |12   |123  |1234 Somewhere|
|Bob       |Other    |NULL |NULL |NULL          |

Is this even possible?

What is your condition ? Please include it.

Avi
– Avi

2019-02-02 00:27:12 +00:00
Commented Feb 2, 2019 at 0:27 — Avi
– Avi, Commented Feb 2, 2019 at 0:27

GMB · Accepted Answer · 2019-02-02 00:41:19Z

2

It should be possible to solve this by puting the subquery in a LEFT JOIN and then using CASE in the SELECT clause to set the output columns to NULL when needed :

SELECT
    p.first_name,
    p.last_name,
    CASE WHEN s.some_id IS NOT NULL THEN p.age     END AS age,
    CASE WHEN s.some_id IS NOT NULL THEN p.phone   END AS phone,
    CASE WHEN s.some_id IS NOT NULL THEN p.address END AS address
FROM people p
LEFT JOIN other_table s ON s.some_id = p.field AND s.field = 'is good'

The thing that you need to be careful about is the cardinality of the join. If several records in other_table match a single people record, then you will get duplicated output rows. To avoid that, one solution would be to use SELECT DISTINCT, or to turn on aggregation by adding clause GROUP BY p.first_name, p.last_name, p.age, p.phone, p.address at the end of the query.

edited Feb 2, 2019 at 0:41

answered Feb 2, 2019 at 0:35

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

FXD · Accepted Answer · 2019-02-02 00:57:54Z

I would recommend using a solution based on EXISTS which will be able to support any kind on multiplicity in other_table.
Note: you usually want to "link" values from people with values from other_table by referencing the correct fields in the sub-query, which I have done for first_name and last_name.

SELECT
    first_name,
    last_name,
    CASE WHEN MyCondition THEN NULL ELSE age     END AS Age,
    CASE WHEN MyCondition THEN NULL ELSE phone   END AS Phone,
    CASE WHEN MyCondition THEN NULL ELSE address END AS Addess
FROM (
SELECT people.*, EXISTS (SELECT 1 FROM other_table WHERE field = 'is good' AND first_name = P.first_name and last_name=P.last_name) as MyCondition
FROM people P
) MyTable

PS: in case you want to extend this to other DBMS, remember that some may not support EXISTS(...) at the place where I typed it (example of such DBMS: Oracle).
In such a case, you need to do a CASE WHEN EXISTS(...) THEN 1 ELSE 0 END instead.

Gordon Linoff · Accepted Answer · 2019-02-02 02:17:15Z

1

You can accomplish this with a lateral join:

select p.first_name, p.last_name, s.*
from people p left join lateral
     (select p.age, p.phone, p.address
      from othertable ot
      where ot.other_id = p.field
     ) s;

The subquery will return no rows if there is not a match -- hence all the columns will be NULL. Otherwise, it will return the fields from people.

answered Feb 2, 2019 at 2:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL - Set certain columns to null based on condition

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related