SQL, Why do I get an extra row of null

There are no matches.  Why do I get a second row of nulls?  (even if there are matches this code returns an extra row of nulls).
@CID = 133

row 1 - 0, 0, 133
row 2 - null, null, null

DECLARE @CIDToBeDeleted Table(
		ID int
		, PID int
		, CID int		
		); 
    -- Insert statements for procedure here
INSERT INTO @CIDToBeDeleted(ID, PID, CID) --seed table with parent node
VALUES (0, 0, @CID)
		
--Association Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT t.ID, t.ParentAssociationID, t.ChildLeagueID
FROM [2Associations] t right outer join
	@CIDToBeDeleted ON CID = ParentAssociationID or CID = ChildLeagueID
	
SELECT ID, PID as ParentID, CID as ContactID
FROM @CIDToBeDeleted	

Open in new window


Thanks Much
Sam
SamCashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
You have 2 insert statements.  The first inserts the row you expect.  The second inserts the all NULL record due to the RIGHT OUTER JOIN.

Comment out line 11 and you will see the row that is being inserted from the second insert.
0
SamCashAuthor Commented:
Brian,

Thanks much.

Yes the second insert line 11, inserts the null row, but there are no records that match the JOIN.  Even when there are matches and 1 or more rows are returned, I seem to always get an extra null row.

I am new to SQL, do RIGHT OUTER JOIN s always return an extra null row?

Regards
Sam
0
chaauCommented:
I think it will help you to better understand your query if you analyse the SELECT query first. Let's modify it to include extra rows from the temp table, like this:
SELECT DISTINCT t.ID, t.ParentAssociationID, t.ChildLeagueID
d.ID, d.PID, d.CID
FROM [2Associations] t right outer join
	@CIDToBeDeleted d ON d.CID = t.ParentAssociationID or d.CID = t.ChildLeagueID

Open in new window

When you execute the above query you will notice all your 11 rows that satisfy the criteria in the ON clause. You will also notice rows that have NULL values in the first three columns. The last three columns, however will have the valid values from the record you have inserted in the previous command.
I think you actually meant to use the INNER join. Try this statement:
SELECT t.ID, t.ParentAssociationID, t.ChildLeagueID
d.ID, d.PID, d.CID
FROM [2Associations] t inner join
	@CIDToBeDeleted d ON d.CID = t.ParentAssociationID or d.CID = t.ChildLeagueID

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SamCashAuthor Commented:
Chaau,

Thanks for the suggestion.  Yes the SELECT has to work right first.  I am building a temp table @CIDToBeDeleted which requires two columns of paired values (Parent and Child), the third column, ID is to help debug.   This INSERT/SELECT query is applied to 7 different tables to get the list to ultimately be deleted.

These paired values will then be used to DELETE rows from an hierarchical table having two columns as follows.  

DELETE
FROM HierarchicalTable inner join
		@CIDToBeDeleted ON PID = ParentClassOfPlayID AND CID = ChildConferenceID

Open in new window


The second SELECT in my example is just to see what is being inserted into the temp table.

I do not see how 6 columns will work?  My issue at this point is the extra null rows I get from each of the 7 tables.  I am getting the correct pairs other than the null rows.  I can get rid of them with a "WHERE ID <> null".  I am worried if I just hack them off it is poor practice and will bite me in the future.  Anyway I need to learn SQL and best practices.  I think I need the RIGHT JOIN @CIDToBeDeleted to keep all the records previously INSERTED to the temp table.

Thanks again for your help.
Sam
0
chaauCommented:
You do not need the RIGHT JOIN to keep the previously inserted records. The INSERT INTO command has nothing to do with the recods that were in the table.

Perhaps, if you provide us with the sample data, your requirements, and the expected results after the DELETE command we will be able to help you build the required queries
0
SamCashAuthor Commented:
Chaau,

I think I have made this more complicated than necessary, sorry.

Sorry to refer to the INSERT INTO,  I am only working on the SELECT only.

SELECT DISTINCT t.ID, t.ParentAssociationID, t.ChildLeagueID
FROM [2Associations] t right join
	@CIDToBeDeleted ON CID = ParentAssociationID or CID = ChildLeagueID

Open in new window


This SELECT query yields the correct results, except an extra row of null

Thanks for the quick response
Sam
0
chaauCommented:
To understand what this extra row is you need to include the columns from the @CIDToBeDeleted table. Have you tried to run the statement I have specified in the previous answer?
SELECT DISTINCT t.ID, t.ParentAssociationID, t.ChildLeagueID,
d.ID, d.PID, d.CID
FROM [2Associations] t right outer join
	@CIDToBeDeleted d ON d.CID = t.ParentAssociationID or d.CID = t.ChildLeagueID

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
RIGHT OUTER JOIN means bring all records from the right table (in your case is @CIDToBeDeleted) and those records that doesn't match the left table ([2Associations] in your case) should be populated with NULL.

You may want something like this:
SELECT DISTINCT t.ID, t.ParentAssociationID, t.ChildLeagueID
FROM [2Associations] t, @CIDToBeDeleted 
WHERE CID = ParentAssociationID or CID = ChildLeagueID

Open in new window

0
SamCashAuthor Commented:
Chaau, Vitor,

Thanks again.  The inner join did the trick of eliminating the extra null records.  I still want to know why a SELECT with a right join always returns one extra null record ?  Neither table has any null records.  Is this just something right joins do, is there a use.  I am sure someday I will need a right join, although I have not tested it I assume a left join behaves the same.  

Below is the final SELECT query and the full usage to build the table.  As mentioned above each hierarchical table has just 3 columns an ID, ParentID, and ChildID(ID is not used).  The 2nd SELECT in each section is just to see the table being built and will not be included in release.  The fun part of this was each parent discovered yielded new parents and their children to be deleted, which yielded new parents and their children...

SELECT DISTINCT d.ID, d.ParentAssociationID, d.ChildLeagueID
FROM [2Associations] d inner join
	@CIDToBeDeleted ON CID = ParentAssociationID or CID = ChildLeagueID

Open in new window


ALTER PROCEDURE [dbo].[ContactsDel] 
	-- Add the parameters for the stored procedure here
	@CID int
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	--This table is seeded with the parent @CID to be deleted and accumulates all the subodinate parent PID and child CID
	--pairs from each of 5 hierarchy tables for the complete unique list of parent child records to be deleted.
	DECLARE @CIDToBeDeleted Table( 
		ID int
		, PID int
		, CID int		
		); 
    -- Insert statements for procedure here
INSERT INTO @CIDToBeDeleted(ID, PID, CID) --Seed table with CID to be deleted
VALUES (0, 0, @CID)
		
--Association Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentAssociationID, d.ChildLeagueID
FROM [2Associations] d inner join
	@CIDToBeDeleted ON CID = ParentAssociationID or CID = ChildLeagueID
	
SELECT ID, PID as ParentID, CID as ContactID_A, FirstName --for bebugging
FROM @CIDToBeDeleted left join
	Contacts ON CID = ContactID
	
--League Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentLeagueID, d.ChildClassOfPlayID
FROM [3Leagues] d inner join
	@CIDToBeDeleted ON CID = ParentLeagueID or CID = ChildClassOfPlayID

SELECT ID, PID as ParentID, CID as ContactID_L, FirstName --for bebugging
FROM @CIDToBeDeleted left join
	Contacts ON CID = ContactID
	
--COP Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentClassOfPlayID, d.ChildConferenceID
FROM [4ClassOfPlay] d inner join 
	@CIDToBeDeleted ON CID = ParentClassOfPlayID or CID = ChildConferenceID

SELECT ID, PID as ParentID, CID as ContactID_CoP, FirstName --for bebugging
FROM @CIDToBeDeleted	left join
	Contacts ON CID = ContactID
	
--Conference Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentConferenceID, d.ChildDivisionID
FROM [5Conferences] d inner join 
	@CIDToBeDeleted ON CID = ParentConferenceID or CID = ChildDivisionID

SELECT ID, PID as ParentID, CID as ContactID_Conf, FirstName --for bebugging
FROM @CIDToBeDeleted left join
	Contacts ON CID = ContactID
	
--Divisions Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentDivisionID, d.ChildTeamID
FROM [6Divisions] d inner join 
	@CIDToBeDeleted ON CID = ParentDivisionID or CID = ChildTeamID

SELECT ID, PID as ParentID, CID as ContactID_Div, FirstName --for bebugging
FROM @CIDToBeDeleted left join
	Contacts ON CID = ContactID

--Final CIDToBeDeleted
SELECT ID, PID as ParentID, CID as ContactID_TBD, FirstName --for bebugging
FROM @CIDToBeDeleted left join
	Contacts ON CID = ContactID

Order by FirstName

Open in new window

0
SamCashAuthor Commented:
Chaau, Vitor,

Here is the result.  I would appreciate any suggestions on "Best Practices" or any way to make this more efficient.

I am still questioning right and left joins always returning one extra null row,  It seems if there are no satisfied criteria in the join, the result is 1 null row, if there are n rows satisfying the criteria the results are n + 1 null row, hmmm?

Thanks again for your assistance.

Regards
Sam

ALTER PROCEDURE [dbo].[ContactsDel] 
	-- Add the parameters for the stored procedure here
	@CID int
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	--This table is seeded with the parent @CID to be deleted and accumulates all the subodinate parent PID and child CID
	--pairs from each of 5 hierarchy tables for the complete unique list of parent child records to be deleted.
	DECLARE @CIDToBeDeleted Table( 
		ID int
		, PID int
		, CID int		
		); 
    -- Insert statements for procedure here
INSERT INTO @CIDToBeDeleted(ID, PID, CID) --Seed table with CID to be deleted
VALUES (0, 0, @CID)
		
--Association Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentAssociationID, d.ChildLeagueID
FROM [2Associations] d inner join
	@CIDToBeDeleted ON CID = ParentAssociationID or CID = ChildLeagueID
	
--SELECT ID, PID as ParentID, CID as ContactID_A, FirstName --for bebugging
--FROM @CIDToBeDeleted left join
--	Contacts ON CID = ContactID
	
--League Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentLeagueID, d.ChildClassOfPlayID
FROM [3Leagues] d inner join
	@CIDToBeDeleted ON CID = ParentLeagueID or CID = ChildClassOfPlayID

--SELECT ID, PID as ParentID, CID as ContactID_L, FirstName --for bebugging
--FROM @CIDToBeDeleted left join
--	Contacts ON CID = ContactID
	
--COP Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentClassOfPlayID, d.ChildConferenceID
FROM [4ClassOfPlay] d inner join 
	@CIDToBeDeleted ON CID = ParentClassOfPlayID or CID = ChildConferenceID

--SELECT ID, PID as ParentID, CID as ContactID_CoP, FirstName --for bebugging
--FROM @CIDToBeDeleted	left join
--	Contacts ON CID = ContactID
	
--Conference Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentConferenceID, d.ChildDivisionID
FROM [5Conferences] d inner join 
	@CIDToBeDeleted ON CID = ParentConferenceID or CID = ChildDivisionID

--SELECT ID, PID as ParentID, CID as ContactID_Conf, FirstName --for bebugging
--FROM @CIDToBeDeleted left join
--	Contacts ON CID = ContactID
	
--Divisions Table
INSERT INTO @CIDToBeDeleted
SELECT DISTINCT d.ID, d.ParentDivisionID, d.ChildTeamID
FROM [6Divisions] d inner join 
	@CIDToBeDeleted ON CID = ParentDivisionID or CID = ChildTeamID

--SELECT ID, PID as ParentID, CID as ContactID_Div, FirstName --for bebugging
--FROM @CIDToBeDeleted left join
--	Contacts ON CID = ContactID

--Final CIDToBeDeleted
SELECT ID, PID as ParentID, CID as ContactID_TBD, FirstName --for bebugging
FROM @CIDToBeDeleted left join
	Contacts ON CID = ContactID
Order by ID

--&&*&*&*&*&*&*   DELETE(s)  &*&*&*&*&*&&*&*&*&
SELECT *
--DELETE
FROM [6Divisions] inner join
		@CIDToBeDeleted ON PID = ParentDivisionID
		
WHERE (PID = ParentDivisionID and CID = ChildTeamID)
	or (PID = ParentDivisionID and ( CID is null and ChildTeamID is null))
			
--Conference
SELECT * 
--DELETE
FROM [5Conferences] inner join
		@CIDToBeDeleted ON PID = ParentConferenceID
		
WHERE (PID = ParentConferenceID and CID = ChildDivisionID)
	or (PID = ParentConferenceID and ( CID is null and ChildDivisionID is null))		
		
--ClassOfPlay
SELECT * 
--DELETE
FROM [4ClassOfPlay] inner join
		@CIDToBeDeleted ON PID = ParentClassOfPlayID
		
WHERE (PID = ParentClassOfPlayID and CID = ChildConferenceID)
	or (PID = ParentClassOfPlayID and ( CID is null and ChildConferenceID is null))
	
--League	
SELECT * 
--DELETE
FROM [3Leagues] inner join
		@CIDToBeDeleted ON PID = ParentLeagueID
		
WHERE (PID = ParentLeagueID and CID = ChildClassOfPlayID)
	or (PID = ParentLeagueID and ( CID is null and ChildClassOfPlayID is null))
	
--Association
SELECT * 
--DELETE
FROM [2Associations] inner join
		@CIDToBeDeleted ON PID = ParentAssociationID
		
WHERE (PID = ParentAssociationID and CID = ChildLeagueID)
	or (PID = ParentAssociationID and ( CID is null and ChildLeagueID is null))

END

Open in new window

0
chaauCommented:
You queries look fine to me. To better understand the INNER vs LEFT join logic consider the following scenario. You trade stock. You are dealing with only a small set of stock items. Let's say there are only three items you are ever interested in: ABC, DEF, GHI. However, you receive the information from the stock exchange for all the stock items.
The table setup is as simple as this:
create table myStock(item varchar(3), price float);
create table allStock(item varchar(3), descr varchar(100), price float);
insert into myStock(item, price) values
('ABC', 1.0),
('DEF', 2.0),
('GHI', 3.0);
insert into allStock(item, descr, price) values
('ABC', 'Company ABC', 1.1),
('DEF', 'Company DEF', 2.2),
('GHI', 'Company GHI', 2.8),
('JKL', 'Company JKL', 10.0),
('MNO', 'Company MNO', 20.1);

Open in new window

Now, if you want to create the report for your stock only and include the prices from the Exchange stock you would use this query:
select m.item, m.price as myPrice, a.price as excPrice, a.descr
FROM myStock m INNER JOIN allStock a
ON a.item = m.item;

| item | price | price |       descr |
|------|-------|-------|-------------|
|  ABC |     1 |   1.1 | Company ABC |
|  DEF |     2 |   2.2 | Company DEF |
|  GHI |     3 |   2.8 | Company GHI |

Open in new window

However, if you also want to see in the same report all stock at the Exchange regarless of if you have this stock or not you would use this:
select m.item, m.price as myPrice, a.price as excPrice, a.descr
FROM myStock m RIGHT JOIN allStock a
ON a.item = m.item;

|   item |  price | price |       descr |
|--------|--------|-------|-------------|
|    ABC |      1 |   1.1 | Company ABC |
|    DEF |      2 |   2.2 | Company DEF |
|    GHI |      3 |   2.8 | Company GHI |
| (null) | (null) |    10 | Company JKL |
| (null) | (null) |  20.1 | Company MNO |

Open in new window

Or no, there are some crazy records with null values in the item! What is going on? The answer is simple. These records do not exist in your table, but exist in the allStock table. If you change the query to the one below you will see the item:

select a.item, m.price as myPrice, a.price as excPrice, a.descr
FROM myStock m RIGHT JOIN allStock a
ON a.item = m.item;

| item |  price | price |       descr |
|------|--------|-------|-------------|
|  ABC |      1 |   1.1 | Company ABC |
|  DEF |      2 |   2.2 | Company DEF |
|  GHI |      3 |   2.8 | Company GHI |
|  JKL | (null) |    10 | Company JKL |
|  MNO | (null) |  20.1 | Company MNO |

Open in new window

SQL Fiddle is here. RIGHT and LEFT joins hould be used in the cases like this, when you want all records from one table regardless of if the matching recods exist in another table. Surely enough you need to include the columns from the other table to see the values, otherwise there will be nulls.
I hope this will help you in your learning
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SamCashAuthor Commented:
Chaau,

Thanks for the tutorial.  

I picked up some more knowledge, I did not realize the SELECT columns effect the null returns, ie if the column is included the result won't be null (unless it is null in the source table) if it is not included in the SELECT then the result will be null, now it is obvious...

High Regards
Sam
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.