Re: Problem with a join due to multiple Nulls

David,

Unfortunately I'm working with data exttracts as opposed to a well
designed system.
The code below demonstrates what i am trying to do.
In TableA My_ID is is either Null or a unique number.
In TableB My_ID is either Null or a number which may not be unique.

I tried using a full outer join the other day, but after 15 hours it
still had not worked so I think I am doing somethiing wrong and need
help.

In my real data TableA contains just under 2 million records 1.2million
of which My_ID is null and TableB conatins 5million records of which
almost 3 million of which My_ID is null.





CREATE TABLE TableA ( My_ID nvarchar(4000),Field1
nvarchar(4000),CounterA nvarchar(4000))
GO

INSERT INTO TableA
SELECT
'1', 'Paul','1'
UNION all SELECT
'2', 'John','1'
UNION all SELECT
'3', 'Mark','1'
UNION all SELECT
Null, 'Simon','1'
UNION all SELECT
Null, 'Peter','1'


CREATE TABLE TableB ( My_ID nvarchar(4000),Field2
nvarchar(4000),CounterB nvarchar(4000))
GO

INSERT INTO TableB
SELECT
'1', '23','1'
UNION all SELECT
'1', '24','1'
UNION all SELECT
'4', '26','1'
UNION all SELECT
Null, '27','1'
UNION all SELECT
Null, '28','1'



SELECT *
FROM TableA FULL OUTER JOIN
TableB ON TableA.My_ID = TableB.My_ID


Regards,
Ciarán

.

Relevant Pages

  • Re: Advanced query issue
    ... FROM tablea As a RIGHT JOIN tableb as b ... That assumes tableb has all the possible occurrence of the two ... I also used UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Advanced query issue
    ... my mistake, in the second query, should have been UNION not UNION ALL, ... If lots of a.c are empty in the first query, it is because they appear ONLY ... FROM tablea As a RIGHT JOIN tableb as b ...
    (microsoft.public.access.queries)
  • Re: Advanced query issue
    ... and then write a query linking just by your new ... FROM tablea As a RIGHT JOIN tableb as b ... That assumes tableb has all the possible occurrence of the two ... I also used UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Full Outer Join
    ... Usng the UNION ALL makes the query run a bit faster than just using UNION ... TableB AS B ON A.FieldName = B.FieldName ... FROM TableA AS C RIGHT JOIN ... >A full outer join is done by unioning a left outer join with a right outer ...
    (microsoft.public.access.queries)
  • Re: Full Outer Join
    ... > Usng the UNION ALL makes the query run a bit faster than just using UNION ... > TableB AS B ON A.FieldName = B.FieldName ... > FROM TableA AS C RIGHT JOIN ... >>A full outer join is done by unioning a left outer join with a right outer ...
    (microsoft.public.access.queries)