How do I return Null in SQL

Question

select 
    ITEM.ITEM_ID, ITEM.ITEM_NO, ITEM_CC_PAT.CC_ID, 
    ITEM_CC_PAT.PAT_CHRG_NO, 
    ITEM_CC_PAT.PAT_CHRG_PRICE
from 
    ITEM, ITEM_CC_PAT
where 
    ITEM.ITEM_ID = ITEM_CC_PAT.ITEM_ID 
    and ITEM.ITEM_NO = '000642'

I'm running this but it isn't returning anything. I know for a fact that were there is an Item.xxxx that there is information there but where there is a ITEM_CC_PAT.xxxx it should return Null in those columns. Does anyone know what I can do?

I tried adding this but it still returned nothing.

Where 
    ITEM_CC_PAT.CC_ID IS NULL
    ITEM_CC_PAT.PAT_CHRG_NO IS NULL
    ITEM_CC_PAT.PAT_CHRG_PRICE IS NULL

First you should take a peek at this and start using ANSI-92 style joins. They have been around for more than 25 years now. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins Using proper joins you would be able to use a left join which is the root of your problem here. — Sean Lange, Nov 28 '17 at 22:22

score 2 · Answer 1 · answered Nov 28 '17 at 22:21

2

You need a left join. Use a modern join to implement that... like this:

select ITEM.ITEM_ID, ITEM.ITEM_NO, ITEM_CC_PAT.CC_ID, 
ITEM_CC_PAT.PAT_CHRG_NO, 
ITEM_CC_PAT.PAT_CHRG_PRICE
from ITEM
left join ITEM_CC_PAT on ITEM.ITEM_ID = ITEM_CC_PAT.ITEM_ID 
where ITEM.ITEM_NO = '000642'

answered Nov 28 '17 at 22:21

Hogan

69,564
10
76
117

Thank you for your help. – Belal Kamara Nov 29 '17 at 14:13
So if I want to add multiple tables should I continue to add left join to each table I add or should I only add left join to the one with NULL values? – Belal Kamara Nov 29 '17 at 14:20
@BelalKamara -- it depends on your data model -- are the joined tables 1 to 1 or 1 to 1..n then you don't need left join. When the tables are 1 to 0..1 or 1 to 0..n then you need a left join. – Hogan Nov 29 '17 at 15:42
@BelalKamara -- In general I use a left join UNLESS I know an inner join enforces a business rule (the joined data is required). – Hogan Nov 29 '17 at 15:43
I believe its a 1 to 1. But I am joining the tables in the where clause. So should I add it in inner join instead? – Belal Kamara Nov 29 '17 at 17:02
@BelalKamara -- joining the tables in the where clause is "old style joins" it is the same as saying `join x on x.a = y.a` which is called an "inner join". – Hogan Nov 29 '17 at 17:18

score 0 · Answer 2 · answered Nov 28 '17 at 22:21

Perhaps a left outer join is what you are looking for.

select ITEM.ITEM_ID, ITEM.ITEM_NO, ITEM_CC_PAT.CC_ID, 
ITEM_CC_PAT.PAT_CHRG_NO, 
ITEM_CC_PAT.PAT_CHRG_PRICE
from ITEM
left outer join ITEM_CC_PAT on ITEM.ITEM_ID = ITEM_CC_PAT.ITEM_ID 
where ITEM.ITEM_NO = '000642'

cadash · Answer 3 · 2017-11-28T22:47:26.273

What you need is a JOIN clause, there are many types INNER JOIN, NATURAL JOIN, LEFT, RIGHT... the left table if the first table you declare, the right table is the second. Left join means all the records in the left table will be tested against the second table. It will then display both matches and NULL when there are no matches.

Inner joins will compare the two tables and only show results when there is a match.

Natural Joins compares all columns that appear with the same name in both tables.

That's the way you work with Primary and Foreign keys in databases.

The query should look something like this:

SELECT i.ITEM_ID, i.ITEM_NO, p.CC_ID, p.PAT_CHRG_NO, p.PAT_CHRG_PRICE
FROM ITEM i INNER JOIN ITEM_CC_PAT p
ON i.ITEM_ID = p.ITEM_ID 
AND i.ITEM_NO = '000642';

Also you should use aliases to make the code shorter and more readable, plus it's the right way to do it. Notice how aliases are created and used E.g.: "ITEMS i or ITEM_CC_PAT p" in this case. Then you can reference the tables by just using i or p as prefixes E.g: i.ITEM_ID, i.ITEM_NO, p.CC_ID, etc

score 0 · Answer 4 · answered Nov 29 '17 at 00:45

I'm with the others, you need to specify the type of JOIN as follows:

SELECT
     ITEM.ITEM_ID
    ,ITEM.ITEM_NO
    ,ITEM_CC_PAT.CC_ID
    ,ITEM_CC_PAT.PAT_CHRG_NO
    ,ITEM_CC_PAT.PAT_CHRG_PRICE
FROM ITEM
LEFT OUTER JOIN ITEM_CC_PAT
   ON ITEM.ITEM_ID = ITEM_CC_PAT.ITEM_ID 
WHERE ITEM.ITEM_NO = '000642'

This will give you all records from the ITEM table while allowing for matches or no matches (NULLs) in the ITEM_CC_PAT table.

How do I return Null in SQL

4 Answers4