How do I write a full outer join query in access

by

Assuming there are not duplicate rows in AA and BB (i.e. all the same values), a full outer join is the equivalent of the union of a left join and a right join.

SELECT *
    FROM AA
        LEFT JOIN BB ON AA.C_ID = BB.C_ID
UNION
SELECT *
    FROM AA
        RIGHT JOIN BB ON AA.C_ID = BB.C_ID

If there are duplicate rows (and you want to keep them), add WHERE AA.C_ID IS NULL at the end, or some other field that is only null if there is not corresponding record from AA.

EDIT:

See a similar approach here.

It recommends the more verbose, but more performant

SELECT *
    FROM AA
        JOIN BB ON AA.C_ID = BB.C_ID
UNION ALL
SELECT *
    FROM AA
        LEFT JOIN BB ON AA.C_ID = BB.C_ID
    WHERE BB.C_ID IS NULL
UNION ALL
SELECT *
    FROM AA
        RIGHT JOIN BB ON AA.C_ID = BB.C_ID
    WHERE AA.C_ID IS NULL

However, this assumes that AA.C_ID and BB.C_ID are not null.

More Related Contents:

  1. sql in ms-access [closed]
  2. Managing and debugging SQL queries in MS Access
  3. SQL multiple join statement
  4. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  5. How to do INSERT into a table records extracted from another table
  6. Multiple INNER JOIN SQL ACCESS
  7. How to simulate UNPIVOT in Access?
  8. Table-less UNION query in MS Access (Jet/ACE)
  9. SQL Query – Using Order By in UNION
  10. Ms Access Query: Concatenating Rows through a query
  11. Top n records per group sql in access
  12. Access-SQL: Inner Join with multiple tables
  13. is there a group_concat function in ms-access?
  14. Address standardization within a database
  15. How do I implement pagination in SQL for MS Access?
  16. SQL to add column with default value – Access 2003
  17. Achieving ROW_NUMBER / PARTITION BY in MS Access
  18. SQL Updating from an inner join
  19. How can I implement SQL INTERSECT and MINUS operations in MS Access
  20. Lightweight SQL database which doesn’t require installation [closed]
  21. Convert String to Date in MS Access Query
  22. HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]
  23. MSAccess – query to return result set of earliest rows with a unique combination of 2 columns
  24. How to save the result of a SQL query into a variable in VBA?
  25. How do I perform update query with subquery in Access?
  26. SQL Update woes in MS Access – Operation must use an updateable query
  27. Select query with date condition
  28. Microsoft Access – Case Query
  29. How do I create a decimal field in Access with Alter Table?
  30. Escaping ‘ in Access SQL

Leave a Comment