Filtering out null values only if they exist in SQL select

Question

Below I have sql select to retrieve values from a table. I want to retrieve the values from tableA regardless of whether or not there are matching rows in tableB. The below gives me both non-null values and null values. How do I filter out the null values if non-null rows exist, but otherwise keep the null values?

SELECT a.* FROM
(
    SELECT
            id,
            col1, 
            coll2
        FROM tableA a LEFT OUTER JOIN tableB b ON b.col1=a.col1 and b.col2='value'
        WHERE a.id= @id
          AND a.col2= @arg

) AS a

 ORDER BY col1 ASC

You're missing a code segment. ) is missing it's friend.

Zane
– Zane

2014-01-24 19:42:45 +00:00
Commented Jan 24, 2014 at 19:42 — Zane
– Zane, Commented Jan 24, 2014 at 19:42

Gordon Linoff · Accepted Answer · 2014-01-24 19:45:33Z

1

You can do this by counting the number of matches using a window function. Then, either return all rows in A if there are no matching B rows, or only return the rows that do match:

select id, col1, col2
from (SELECT a.id, a.col1, a.coll2,
             count(b.id) over () as numbs
      FROM tableA a LEFT OUTER JOIN tableB b ON b.col1=a.col1 and b.col2='value'
      WHERE a.id = @id AND a.col2= @arg
     ) ab
where numbs = 0 or b.id is not null;

answered Jan 24, 2014 at 19:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Marko Juvančič · Accepted Answer · 2014-01-24 19:42:58Z

1

Filter them out in WHERE clause

SELECT
    id,
    col1, 
    coll2
FROM tableA a LEFT OUTER JOIN tableB b ON b.col1=a.col1 and b.col2='value'
WHERE a.id= @id
  AND a.col2= @arg
  AND A.Col1 IS NOT NULL -- HERE
) AS a

ORDER BY col1 ASC

answered Jan 24, 2014 at 19:42

Marko Juvančič

5,9201 gold badge27 silver badges43 bronze badges

Comments

CRAFTY DBA · Accepted Answer · 2014-01-24 19:58:53Z

For some reason, people write code like Marko that puts a filter (b.col2 = 'value') in the JOIN clause. While this works, it is not good practice.

Also, you should get in the habit of having the ON clause in the right sequence. We are joining table A to table B, why write it as B.col1 = A.col1 which is backwards.

While the above statement works, it could definitely be improved.

I created the following test tables.

-- Just playing
use tempdb;
go

-- Table A
if object_id('A') > 0 drop table A
go
create table A
(
  id1 int,
  col1 int,
  col2 varchar(16)
);
go

-- Add data
insert into A
values
(1, 1, 'Good data'),
(2, 2, 'Good data'),
(3, 3, 'Good data');

-- Table B
if object_id('B') > 0 drop table B
go
create table B
(
  id1 int,
  col1 int,
  col2 varchar(16)
);

-- Add data
insert into B
values
(1, 1, 'Good data'),
(2, 2, 'Good data'),
(3, NULL, 'Null data');

Here is the improved statement. I choose literals instead of variables. However, you can change for your example.

-- Filter non matching records
SELECT 
    A.*
FROM A LEFT OUTER JOIN B ON 
    A.col1 = B.col1 
WHERE 
    B.col1 IS NOT NULL AND
    A.id1 in (1, 2) AND
    A.col2 = 'Good data'
ORDER BY
    A.id1 DESC

Here is an image of the output.

enter image description here

Collectives™ on Stack Overflow

Filtering out null values only if they exist in SQL select

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