Best way to do a FULL JOIN?

[AnotherJob]

What's the best way to implement a FULL JOIN in Jet SQL? Currently I'm using the distinct union of a LEFT OUTER JOIN and RIGHT OUTER JOIN. Is there a more efficient way?

 

[lameid]

Make a left join.

Union

Right join where [left side key] is null.

This way you should get any duplicates that a full join would produce. Also I think this will be faster as I think that distinct is inherently slow.

 

[AnotherJob]

I don't quite follow that. Duplicates are what I don't want. Let me give a simplified example . . .

Suppose I have two tables:

Table 1: Name, Employee-ID

Table 2: Address, Employee-ID

I want to join the records from the two tables on Employee-ID, and store them into a third table:

Table 3: Name, Employee-ID, Address

I don't want duplicates. I want the intersection of the two tables, but I also want the sets of records from each table that don't match on Employee-ID. What is the most efficient way to do that in Jet SQL? Thank you.

 

[lespaul]

maybe:

SELECT Name, Employee-ID, Address 
FROM Table1
LEFT JOIN Table2 ON Table1.Employee-ID = Table2.EmployeeID
UNION
SELECT Name, Employee-ID, Address 
FROM Table2
LEFT JOIN Table1 ON Table2.Employee-ID = Table1.EmployeeID

Leslie

Have you met Hardy Heron?

 

[lameid]

I was thinking something a little different than Leslie...
Both should work as long as Employee-ID is unique in both tables.

SELECT Name, [b]Table1.[/b]Employee-ID, Address 
FROM Table1
LEFT JOIN Table2 ON Table1.Employee-ID = Table2.EmployeeID
UNION ALL
SELECT Name, [b]Table2[/b].Employee-ID, Address 
FROM Table2
LEFT JOIN Table1 ON Table2.Employee-ID = Table1.EmployeeID
[b]Where Table1.Employee-ID IsNULL[/b]

I tend to think about Unions as if Union ALL is used. Although UNION would prevent duplicates. Leslie's code is shorter. I'd be curious as to which is faster as both seem like they should be relatively efficient.

 

[AnotherJob]

That does it! Lameid's pair of joins, with a check for NULL, does a distinct full join, which is what I want.

I've been doing the same as lespaul posted, and then taking distinct records to remove the duplicates. That is probably less efficient since removing the duplicates doubtless involve an additional short.

 

[lameid]

Interesting. Because Leslie's SQL uses Union as opposed to Union all, I would have expected it to remove duplicates, making them functionally equivalent. I did read correctly that her query did create duplicates, right?

 

[AnotherJob]

Upon retesting I see that it does NOT create duplicate keys. However an empty row seems to be coming from somewhere, and I can't seem to extend it to cover additional tables.

 

[lameid]

I'm not sure what you mean by the following.

I can't seem to extend it to cover additional tables

The empty row problem does not make sense to me at all as I really expect them to return the same records, probably sorted differently.