Removing Null Value in SQL Join and Union Operators in SQL

Removing NULL value in SQL JOIN and UNION Operators in SQL

You can not remove only that field in a row. However, you can get rid of the entire row simply by using a WHERE clause to filter out records with null values.

http://sqlfiddle.com/#!9/f77862/42

SELECT
m.provider_id,
m.provider_name,
p.purchase_order_code,
NULL AS purchase_order_sample_code,
p.provider_id AS provider_order
FROM mst_provider AS m
LEFT JOIN trx_purchase_order AS p
ON (m.provider_id = p.provider_id)
WHERE p.provider_id != NULL
UNION
SELECT
m.provider_id,
m.provider_name,
p.purchase_order_code,
NULL AS purchase_order_sample_code,
p.provider_id AS provider_order
FROM mst_provider AS m
RIGHT JOIN trx_purchase_order AS p
ON (m.provider_id = p.provider_id)
UNION
SELECT
m.provider_id,
m.provider_name,
NULL,
s.purchase_order_sample_code,
s.provider_id
FROM mst_provider AS m
RIGHT JOIN trx_purchase_order_sample AS s
ON (s.provider_id = m.provider_id)

Eliminating NULL values from the UNION set operators

You can try below -

SELECT
max(addition) as addition,max(subtraction) as subtraction, max(multiplication) as multiplication,max(division) as division
FROM
(
(SELECT
6 + 2 AS addition,
NULL AS subtraction,
NULL AS multiplication,
NULL AS division
FROM
DUAL)
UNION
(SELECT
NULL AS addition,
6 - 2 AS subtraction,
NULL AS multiplication,
NULL AS division
FROM
DUAL)
UNION
(SELECT
NULL AS addition,
NULL AS subtraction,
6 * 2 AS multiplication,
NULL AS division
FROM
DUAL)
UNION
(SELECT
NULL AS addition,
NULL AS subtraction,
NULL AS multiplication,
6 / 2 AS division
FROM
DUAL)
) A;

How to remove null rows in when using union all in multiple tables

You can't use t.Balance > 0 in your where clause, as Balance is just a alias name to your column in select.

Instead you can write HAVING isnull(sum(v1),0.00) > 0 after GROUP BY.

You final query should look like following.

SELECT ..., isnull(sum(v1),0.00) Balance
FROM
(
--Your internal query here
) T
GROUP BY t.MNO,MNAME
HAVING isnull(sum(v1),0.00) > 0

Another approach is to again wrap you entire query inside a table and put the condition. Like following query.

 select * from
(
select ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [SNO],t.MNO,MNAME ,isnull(sum(v1),0.00) as Balance,isnull(sum(v2),0.00) as CurrentPurchase,isnull(sum(v3),0.00) as Deduction
from (select MNO, PendingDeduc as v1, NULL as v2, NULL as v3
from tblProductPurchaseBalance where EntryDate between @FromDate and @ToDate union all
select MNO, NULL as v1, TotalAmount, NULL as v3
from tblMnoProductPurchase where PurchaseDate between @FromDate and @ToDate union all
select MemNo as MNO, NULL as v1, NULL as v2, AAVIN
from tblDeduction where EntryDate between @FromDate and @ToDate
) t inner join TBLMEMBERS on t.MNO=TBLMEMBERS.MNO
group by t.MNO,MNAME
)t
where T.Balance > 0 OR T.CurrentPurchase > 0 OR T.Deduction > 0
order by t.MNO

How to combine (merge) similar columns to remove NULLs via JOIN

Just include a CASE to select what data use

SELECT      tc."cust_id",
CASE WHEN tp."forename" IS NULL
THEN tcp."name"
ELSE CONCAT(tp."forename", ' ', tp."surname")
AS "name"
FROM "tbl_customer" AS tc
LEFT JOIN "tbl_person" AS tp
ON tc."cust_id" = tp."cust_id"
LEFT JOIN "tbl_company" AS tcp
ON tc."cust_id" = tcp."cust_id"

SQL - Insert NULL entry with Union then order by results

Try using another column:

SELECT  t.ID, t.description, 2 AS o
FROM [dbo].[HouseType] AS t
WHERE [TypeId] = COALESCE(@TypeId, [TypeId])

UNION ALL

SELECT NULL, 'ALL', 1 AS o
ORDER BY o, [TypeDesc]

This way ALL will always precede the results of your original query.

Note: As already noted in a comment there must be a match between the number and type of fields of the subqueries used in the UNION operation.

Note 2: As noted in the other comment by @A ツ using UNION ALL is always preferable over UNION, so use this instead when you have no worries about duplicate values.

SQL Server JOIN missing NULL values

You can be explicit about the joins:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
Table2
ON (Table1.Col1 = Table2.Col1 or Table1.Col1 is NULL and Table2.Col1 is NULL) AND
(Table1.Col2 = Table2.Col2 or Table1.Col2 is NULL and Table2.Col2 is NULL)

In practice, I would be more likely to use coalesce() in the join condition:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
Table2
ON (coalesce(Table1.Col1, '') = coalesce(Table2.Col1, '')) AND
(coalesce(Table1.Col2, '') = coalesce(Table2.Col2, ''))

Where '' would be a value not in either of the tables.

Just a word of caution. In most databases, using any of these constructs prevents the use of indexes.

Order By with Union and null values SQL Server

You are sorting the entire result, not just the records from the second select.

You can use the [Identifier] field to keep the records from the first select first:

order by [Identifier], [Error Flag] desc


Related Topics



Leave a reply



Submit