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
Loop Through Table Rows and Call Stored Procedure on Every Row
How to Delete Multiple Rows in Diferent Tables
Sql - Finding Students Taking 2 or More Classes
Mysql Error 1449: the User Specified as a Definer Does Not Exist
Trim Whitespaces (New Line and Tab Space) in a String in Oracle
How to Handle Duplicates Created by Left Join
Repeat Each Value in SQL Table N Times With 1:N in Different Column
How to Check in Sqlite Whether a Table Exists
Solve Query for Showing Top 5 Selling Products
Update Only Time from My Datetime Field in SQL
How to Divide Sum in Number from Count in Having
Reactjs Connection With Database
Calculating Age Derived from Current Date and Dob
Remove Blank Line in Between Select Queries When Spooling to CSV File
How to Replace Empty String With Value That Is Not Empty for the Same Policynumber
Sql Server How to Find the Customers Who Have Bought a Product from Each Store
Spark - Query Dataframe Based on Values from a Column in Another Dataframe